CREATE PROCEDURE [UP_CU_GETPAGED]
(
@NAMEOREMAIL varchar(30),
@ALLOWLOGIN bit,
@RSTARTTIME datetime,
@RENDTIME datetime,
@LSTARTTIME datetime,
@LENDTIME datetime,
@PAGEINDEX int,
@PAGESIZE int,
@ORDERBY varchar(100)
)
AS
DECLARE @SQL nvarchar(2000)
DECLARE @RecordCount int
SET @SQL = N'
SELECT @RecordCount = count([CU_NAME]) FROM [CU_CUSTOMER] WHERE
(@NAMEOREMAIL IS NULL OR [CU_NAME] LIKE ''%'' + @NAMEOREMAIL + ''%'' OR [CU_EMAIL] LIKE ''%'' + @NAMEOREMAIL + ''%'') AND
(@ALLOWLOGIN IS NULL OR [CU_ALLOWLOGIN] = @ALLOWLOGIN) AND
(@RSTARTTIME IS NULL OR [CU_REGISTERTIME] > @RSTARTTIME) AND
(@RENDTIME IS NULL OR [CU_REGISTERTIME] < @RENDTIME) AND
(@LSTARTTIME IS NULL OR [CU_LASTLOGINTIME] > @LSTARTTIME) AND
(@LENDTIME IS NULL OR [CU_LASTLOGINTIME] < @LENDTIME) ORDER BY @ORDERBY
SELECT @RecordCount
'
EXEC sp_executesql @SQL,N'@RecordCount int output'
GO执行后发生错误消息 137,级别 15,状态 2,第 3 行
必须声明标量变量 "@NAMEOREMAIL"。
'
SELECT @RecordCount = count([CU_NAME]) FROM [CU_CUSTOMER] WHERE
'+@NAMEOREMAIL+' IS NULL OR [CU_NAME] LIKE %'
....
EXEC sp_executesql 需要声明所有的变量
SELECT @RecordCount = count([CU_NAME]) FROM [CU_CUSTOMER] WHERE
(@NAMEOREMAIL IS NULL OR [CU_NAME] LIKE ''%' + @NAMEOREMAIL + '%'' OR [CU_EMAIL] LIKE ''%' + @NAMEOREMAIL + '%'') AND
(@ALLOWLOGIN IS NULL OR [CU_ALLOWLOGIN] = '+@ALLOWLOGIN+') AND
(@RSTARTTIME IS NULL OR [CU_REGISTERTIME] > '+@RSTARTTIME+') AND
(@RENDTIME IS NULL OR [CU_REGISTERTIME] < '+@RENDTIME+') AND
(@LSTARTTIME IS NULL OR [CU_LASTLOGINTIME] > '+@LSTARTTIME+') AND
(@LENDTIME IS NULL OR [CU_LASTLOGINTIME] < '@LENDTIME') ORDER BY '+@ORDERBY'+
SELECT '+@RecordCount
SET @SQL = N'
SELECT @RecordCount = count([CU_NAME]) FROM [CU_CUSTOMER] WHERE
(@NAMEOREMAIL IS NULL OR [CU_NAME] LIKE ''%'' + @NAMEOREMAIL + ''%'' OR [CU_EMAIL] LIKE ''%'' + @NAMEOREMAIL + ''%'') AND
(@ALLOWLOGIN IS NULL OR [CU_ALLOWLOGIN] = @ALLOWLOGIN) AND
(@RSTARTTIME IS NULL OR [CU_REGISTERTIME] > @RSTARTTIME) AND
(@RENDTIME IS NULL OR [CU_REGISTERTIME] < @RENDTIME) AND
(@LSTARTTIME IS NULL OR [CU_LASTLOGINTIME] > @LSTARTTIME) AND
(@LENDTIME IS NULL OR [CU_LASTLOGINTIME] < @LENDTIME) ORDER BY @ORDERBY
SELECT @RecordCount
'
将上面的参数部分换成字符串连接的形式,如
(@ALLOWLOGIN IS NULL OR [CU_ALLOWLOGIN] = @ALLOWLOGIN) AND 改成
'...('+@ALLOWLOGIN+' is null OR [CU_ALLOWLOGIN] = '+@ALLOWLOGIN+') AND'最后sp_executesql处,把所有的参数信息都加上,然后再把参数的值都写上
这里要把其他的变量也写进去
SET @SQL = N'
SELECT @RecordCount = count([CU_NAME]) FROM [CU_CUSTOMER] WHERE
(@NAMEOREMAIL IS NULL OR [CU_NAME] LIKE ''%' + @NAMEOREMAIL + '%'' OR [CU_EMAIL] LIKE ''%' + @NAMEOREMAIL + '%'') AND
(@ALLOWLOGIN IS NULL OR [CU_ALLOWLOGIN] = '+@ALLOWLOGIN+') AND
(@RSTARTTIME IS NULL OR [CU_REGISTERTIME] > '+@RSTARTTIME+') AND
(@RENDTIME IS NULL OR [CU_REGISTERTIME] < '+@RENDTIME+') AND
(@LSTARTTIME IS NULL OR [CU_LASTLOGINTIME] > '+@LSTARTTIME+') AND
(@LENDTIME IS NULL OR [CU_LASTLOGINTIME] < '@LENDTIME') ORDER BY '+@ORDERBY'+
SELECT '+@RecordCount