问题描述
我编写了这个程序来为 gridview 获取页面明智的记录.一切都很好,但现在还需要获取记录计数作为输出参数.例如,如果与名称匹配的总记录数为 100,则查询应生成一些记录并输出数字 100.记录部分正在工作.我怎样才能得到计数.
I wrote this procedure to get records page wise for a gridview. All was fine, but now it is also required to get a count of records as output parameter. For eg if the total records that match a name are 100, then the query should result some of the records and also output the number 100. The records part is working. How can I get the count too.
ALTER STORED PROCEDURE GetData @SearchText nvarchar(50), @SortOrder nchar(10), @ColName nvarchar(20), @StartIndex int, @PageSize int, @RecCount int output AS BEGIN DECLARE @Query nvarchar(max), @Params nvarchar(max) IF @SearchText = '' SET @SearchText = null ELSE SET SearchText = '''%'+@SearchText+'%''' SET @Params = '@StartIndex int, @PageSize int, @RecCount int output' SET @Query = 'WITH TBL AS ( SELECT * FROM tblEmployee WHERE ('+@ColName+' LIKE '+@SearchText+' OR '+@SearchText+' IS NULL) AND DELETED = 0; SELECT @RecCount = @@ROWCOUNT ) SELECT ROW_NUMBER() OVER(ORDER BY '+@ColName+' '+@SortOrder+' )Row, * INTO #Result FROM TBL SELECT * FROM #Result Where Row BETWEEN @StartIndex AND @PageSize DROP TABLE #Result' Execute sp_Executesql @Query, @Params, @StartIndex,@PageSize, @RecordCount output SELECT @RecCount
推荐答案
你需要做这样的事情
DECLARE @Table NVARCHAR(MAX); DECLARE @ColName NVARCHAR(128) = 'Collumn_Name' DECLARE @SearchText NVARCHAR(4000) = 'Search_Word' SET @Table = 'SELECT * FROM tblEmployee WHERE ('+ QUOTENAME(@ColName) +' LIKE @SearchText OR @SearchText IS NULL)' Execute sp_Executesql @Table , N'@SearchText NVARCHAR(4000)' , @SearchText
向 sp_Executesql 传递参数可以保护您免受 sql 注入攻击.
Passing parameter to sp_Executesql protects you against sql injection attack.
还有
就 OUTPUT 而言,此查询返回一个表,您无法将其保存为一个参数.如果您尝试检索一个值,则可以使用 OUTPUT 参数.
As far as OUTPUT is concerned this query returns a table, you cannot save it to one parameter. you can use OUTPUT parameter if you are trying to retrieve one value.
要将 OUTPUT 与您的动态 sql 一起使用,您需要执行以下操作....
To use OUTPUT with your dynamic sql you will need to do something like this....
DECLARE @Table NVARCHAR(MAX); DECLARE @ColName NVARCHAR(128) = 'ColumnName' DECLARE @SearchText NVARCHAR(4000) = 'Search_Word' DECLARE @Out_Param INT OUTPUT SET @Table = N'SELECT * FROM tblEmployee WHERE ('+ QUOTENAME(@ColName) + N' LIKE @SearchText OR @SearchText IS NULL) ' + N'SELECT @Out_Param = @@ROWCOUNT' Execute sp_Executesql @Table , N'@SearchText NVARCHAR(4000), @Out_Param INT OUTPUT' , @SearchText , @Out_Param OUTPUT --<- use OUTPUT key word here SELECT @Out_Param
更新
对了,我在您的查询中修复了近 10 件不同的事情,无法解释所有内容,但比较您的查询和我现在编写的查询从 ALTER STORED PROCEDURE GetData 开始
Right I have fixed almost10 different things in your query cant explain everything but the compare the query you had and the query I have written now start from ALTER STORED PROCEDURE GetData
ALTER PROCEDURE GetData @SearchText NVARCHAR(50), @SortOrder NVARCHAR(10), @ColName NVARCHAR(120), @StartIndex INT, @PageSize INT, @RecCount INT OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE @Query nvarchar(max); IF (@SearchText = '') BEGIN SET @SearchText = null END ELSE BEGIN SET @SearchText = '''%'+ @SearchText +'%''' END SET @Query = N'WITH TBL AS ( SELECT *, ROW_NUMBER() OVER(ORDER BY '+ QUOTENAME(@ColName) + N' @SortOrder ) As Row FROM tblEmployee WHERE ( '+ QUOTENAME(@ColName) + N' LIKE @SearchText OR @SearchText IS NULL) AND DELETED = 0 ) SELECT * INTO #Result FROM TBL SELECT @RecCount = @@ROWCOUNT; SELECT * FROM #Result Where Row BETWEEN @StartIndex AND @PageSize DROP TABLE #Result' Execute sp_Executesql @Query , N'@SearchText NVARCHAR(50),@SortOrder NVARCHAR(10),@StartIndex INT,@PageSize INT,@RecCount INT OUTPUT' , @SearchText , @SortOrder , @StartIndex , @PageSize , @RecCount OUTPUT SELECT @RecCount END