問題描述

我編寫了這個程序來為 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

這篇關于動態 Sql 的輸出參數的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！