Output Parameter from Dynamic Sql

问题:

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

回答1:

You need to do something like this

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

Passing parameter to sp_Executesql protects you against sql injection attack.

Also

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.

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

UPDATE

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

回答2:

This is a formatted comment. It is not an attempt to answer.

Are you sure all was fine? Let's say that your c# program was passing an @ColName of "firstname" and an @SearchText of "fred". That means this code:

SELECT * FROM tblEmployee 
WHERE ('+@ColName+' LIKE '+@SearchText+' OR '+@SearchText+' 
IS NULL)

becomes

SELECT * FROM tblEmployee 
WHERE (firstname LIKE 'fredORfred' 
IS NULL)

First of all, it is not valid sql. It will throw a syntax error because you can't have like something and is null together like that.

Secondly, why do you have "or @SearchText"? It pretty much guarantees that no records will be found.

Finally, did your c# program surround the SearchText variable with percentage signs? If not, your like keyword is behaving like an equal sign.