首页 新闻 赞助 找找看

sqlserver 存储过程问题

0
悬赏园豆:10 [已解决问题] 解决于 2015-04-21 15:25

这种问题怎么处理,给count赋值

 

 

USE [elaq_system_manager] GO /****** Object:  StoredProcedure [dbo].[proc_SelectEvaluationWeiPingFen_FenYe]    Script Date: 04/21/2015 09:46:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO

-- ============================================= -- Author:============================================= ALTER PROCEDURE [dbo].[proc_SelectTJSafeRecord]

@pageIndex int,

@pageSize INT,

@sort NVARCHAR(200),

@searchInfo NVARCHAR(1000)

AS

BEGIN  

BEGIN  

declare @sql varchar(max)

 DECLARE @count INT     

set @sql = 'SELECT  '+cast(@count as VARCHAR(10))+'= COUNT(1) FROM (SELECT ID    FROM dbo.ProjectOverview WHERE '+@searchInfo+') AS Tabs    SELECT TOP '+cast(@pageSize as VARCHAR(10))     +' * FROM (SELECT ROW_NUMBER()OVER(ORDER BY '+@sort+') ID,'+cast(@count as VARCHAR(10))+' as total,BelongedTo,RecordNumber,BelongsDepartments,ProjectName,ProjectAddress,   dbo.Fun_RetSanFangUnitInfoByRecordNumberAndBelongedTo(RecordNumber,''施工单位'',BelongedTo) AS SGDW,   dbo.Fun_RetSanFangUnitInfoByRecordNumberAndBelongedTo(RecordNumber,''建设单位'',BelongedTo) AS JSDW,   dbo.Fun_RetSanFangUnitInfoByRecordNumberAndBelongedTo(RecordNumber,''监理单位'',BelongedTo) AS JLDW,   ProjectPrice,ProjectStartDateTimne,   ProjectEndDateTimne,WhetherToStartLBS,GroupName FROM dbo.ProjectOverview WHERE '+@searchInfo+') D   WHERE ID > '+cast(@pageSize as VARCHAR(10))+'*('+ cast(@pageIndex as VARCHAR(10)) +'-1) ORDER BY '+@sort  exec (@sql)  

END

END

EXEC proc_SelectTJSafeRecord 1,20,'ID ASC','1=1'

 

最初的、天空的主页 最初的、天空 | 初学一级 | 园豆:40
提问于:2015-04-21 14:34
< >
分享
最佳答案
0

将第一段SQL也放在@sql中

收获园豆:10
dudu | 高人七级 |园豆:31075 | 2015-04-21 14:42

好像不行。 是否是这样?

最初的、天空 | 园豆:40 (初学一级) | 2015-04-21 14:45

@最初的、天空: 建议把SQL语句作为文本贴出来,这样别人可以直接改

dudu | 园豆:31075 (高人七级) | 2015-04-21 14:51

@dudu:  BEGIN
 declare @sql varchar(max)
 DECLARE @count INT
  
 set @sql = 'SELECT  '+cast(@count as VARCHAR(10))+'= COUNT(1) FROM (SELECT ID
   FROM dbo.ProjectOverview WHERE '+@searchInfo+') AS Tabs
   SELECT TOP '+cast(@pageSize as VARCHAR(10))
    +' * FROM (SELECT ROW_NUMBER()OVER(ORDER BY '+@sort+') ID,'+cast(@count as VARCHAR(10))+' as total,BelongedTo,RecordNumber,BelongsDepartments,ProjectName,ProjectAddress,
  dbo.Fun_RetSanFangUnitInfoByRecordNumberAndBelongedTo(RecordNumber,''施工单位'',BelongedTo) AS SGDW,
  dbo.Fun_RetSanFangUnitInfoByRecordNumberAndBelongedTo(RecordNumber,''建设单位'',BelongedTo) AS JSDW,
  dbo.Fun_RetSanFangUnitInfoByRecordNumberAndBelongedTo(RecordNumber,''监理单位'',BelongedTo) AS JLDW,
  ProjectPrice,ProjectStartDateTimne,
  ProjectEndDateTimne,WhetherToStartLBS,GroupName FROM dbo.ProjectOverview WHERE '+@searchInfo+') D
  WHERE ID > '+cast(@pageSize as VARCHAR(10))+'*('+ cast(@pageIndex as VARCHAR(10)) +'-1) ORDER BY '+@sort
 exec (@sql)
 END

最初的、天空 | 园豆:40 (初学一级) | 2015-04-21 14:58

@最初的、天空: 把“DECLARE @count INT”也放到@sql中

dudu | 园豆:31075 (高人七级) | 2015-04-21 15:16
其他回答(1)
0

可以使用临时表做一个转换。

CREATE TABLE #t
(
     a INT
)

EXEC('insert INTO #t SELECT 1')

DECLARE @count INT

SELECT TOP 1 @count=a FROM #t

PRINT @count 
幻天芒 | 园豆:37175 (高人七级) | 2015-04-21 14:59

SELECT TOP 1 @count=a FROM #t  -- 这个后边我要传递一个WHERE '+@searchInfo+' 参数的

支持(0) 反对(0) 最初的、天空 | 园豆:40 (初学一级) | 2015-04-21 15:02

@最初的、天空: 都是动态sql了,你随便拼接。

支持(0) 反对(0) 幻天芒 | 园豆:37175 (高人七级) | 2015-04-21 15:07
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册