动态 Sql 的输出参数

2021-09-10 00:00:00 tsql sql-server

我编写了这个程序来为 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

相关文章