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'
最佳答案
0
将第一段SQL也放在@sql中
收获园豆：10
好像不行。 是否是这样？
@最初的、天空: 建议把SQL语句作为文本贴出来，这样别人可以直接改
@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
@最初的、天空: 把“DECLARE @count INT”也放到@sql中
其他回答(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
SELECT TOP 1 @count=a FROM #t -- 这个后边我要传递一个WHERE '+@searchInfo+' 参数的
@最初的、天空: 都是动态sql了，你随便拼接。
