Ouput parameter from dynamic sql

Posted by Sharpcnet under Sql Server on 2/16/2014 | Points: 10 | Views : 2881 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Bandi on: 2/18/2014 [Member] [MVP] Platinum | Points: 25

0	Refer ALTER PROCEDURE GetData ( @SearchText nvarchar(50), @SortOrder nchar(10), @ColName nvarchar(20), @StartIndex int, @PageSize int) AS BEGIN DECLARE @Query nvarchar(max) , @param VARCHAR(MAX) IF @SearchText = '' SET @SearchText = null ELSE SET @SearchText = '''%'+@SearchText+'%''' SET @Query = ' ;WITH TBL AS ( SELECT * FROM tblEmployee WHERE ('+@ColName+' LIKE '+@SearchText+' OR '+@SearchText+' IS NULL) AND DELETED = 0 ) SELECT ROW_NUMBER() OVER(ORDER BY '+@ColName+' '+@SortOrder+' )Row, * INTO #Result FROM TBL SELECT @@ROWCOUNT as RecCnt; SELECT * FROM #Result Where Row BETWEEN '+CAST(@StartIndex as VARCHAR) + ' AND ' + CAST(@PageSize as VARCHAR) + '; DROP TABLE #Result ' EXEC ( @query) END ----GO GO -- Test Procedure Execute GetData @SearchText = 'a', @SortOrder = 'DESC', @ColName= 'LAST_NAME', @StartIndex = 1, @PageSize = 10 References for accessing more than one result sets from a stored procedure in C# http://stackoverflow.com/questions/18510901/return-multiple-recordsets-from-stored-proc-in-c-sharp http://docs.telerik.com/data-access/developers-guide/low-level-ado-api/executing-stored-procedures/data-access-tasks-adonet-stored-procedures-multiple-result-set Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Refer

ALTER PROCEDURE GetData (

@SearchText nvarchar(50), 
 
@SortOrder nchar(10), 
 
@ColName nvarchar(20), 
 
@StartIndex int, 
 
@PageSize int)
 
AS 
 
BEGIN 
 
    DECLARE @Query nvarchar(max) , @param VARCHAR(MAX)
 
 
    IF @SearchText = '' 
 
    SET @SearchText = null 
 
    ELSE 
 
    SET @SearchText = '''%'+@SearchText+'%''' 
 
    SET @Query = ' ;WITH TBL AS 
 
                  ( 
 
                   SELECT * FROM tblEmployee   
 
                   WHERE ('+@ColName+' LIKE '+@SearchText+' OR '+@SearchText+' IS NULL) AND DELETED = 0
                   )  
                  SELECT ROW_NUMBER() OVER(ORDER BY '+@ColName+' '+@SortOrder+' )Row, * INTO #Result FROM TBL
   
                       SELECT @@ROWCOUNT as RecCnt; 
 
                   SELECT * FROM #Result Where Row BETWEEN '+CAST(@StartIndex as VARCHAR) + ' AND ' + CAST(@PageSize as VARCHAR) + ';
                   DROP TABLE #Result '            

	EXEC ( @query)
END
----GO
GO

-- Test Procedure
 Execute GetData @SearchText = 'a', @SortOrder = 'DESC', @ColName= 'LAST_NAME', @StartIndex = 1, @PageSize = 10

References for accessing more than one result sets from a stored procedure in C#
http://stackoverflow.com/questions/18510901/return-multiple-recordsets-from-stored-proc-in-c-sharp
http://docs.telerik.com/data-access/developers-guide/low-level-ado-api/executing-stored-procedures/data-access-tasks-adonet-stored-procedures-multiple-result-set

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/18/2014 [Member] [MVP] Platinum | Points: 25

0	Refer http://sequelserver.blogspot.in/2007/01/return-value-from-dynamic-sql.html Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sharpcnet on: 2/19/2014 [Member] Starter | Points: 25

0	@Bandi, I see that you have changed select @@Rowcount as reccnt. But where is reccnt being assigned to @RecordCount. Is that qry running for you I put select @@Rowcount as @Rowcount. It says incorrect syntax near @rowcount. I understand, but dont know the right way. I also dont see an output param in ur query. Could you plz tell how exactly it should be. Referring a 'this might work example' is hard to figure out for my level. Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

@Bandi, I see that you have changed select @@Rowcount as reccnt. But where is reccnt being assigned to @RecordCount. Is that qry running for you

I put select @@Rowcount as @Rowcount. It says incorrect syntax near @rowcount. I understand, but dont know the right way.

I also dont see an output param in ur query. Could you plz tell how exactly it should be. Referring a 'this might work example' is hard to figure out for my level.

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 2/19/2014 [Member] [MVP] Platinum | Points: 25

0	Refer this link http://stackoverflow.com/questions/9916154/dynamic-sql-with-output-parameter-syntax-clarity-needed Try to tweet your code.. If NOT working, revert us back Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 2/21/2014 [Member] [MVP] Platinum | Points: 25

0	-- Working Sample Procedure for above requirement.. CREATE PROCEDURE TestDynProc ( @SearchText nvarchar(50), @SortOrder nchar(10), @ColName nvarchar(20), @StartIndex int, @PageSize int, @RecordCountOut INT OUT) AS BEGIN DECLARE @Query nvarchar(max) , @param NVARCHAR(MAX) = '@RecordCount INT OUT', @RecordCount INT IF @SearchText = '' SET @SearchText = null ELSE SET @SearchText = '''%'+@SearchText+'%''' SET @Query = ' ;WITH TBL AS ( SELECT * FROM tblEmployee WHERE ('+@ColName+' LIKE '+@SearchText+' OR '+@SearchText+' IS NULL) AND DELETED = 0 ) SELECT ROW_NUMBER() OVER(ORDER BY '+@ColName+' '+@SortOrder+' )Row, * INTO #Result FROM TBL SELECT @RecordCount = @@ROWCOUNT ; SELECT * FROM #Result Where Row BETWEEN '+CAST(@StartIndex as VARCHAR) + ' AND ' + CAST(@PageSize as VARCHAR) + '; DROP TABLE #Result ' EXECUTE sp_executesql @query, @Param, @RecordCount OUT SELECT @RecordCountOut = @RecordCount; END GO --Test Procedure execution DECLARE @SearchText nvarchar(50), @SortOrder nchar(10), @ColName nvarchar(20), @StartIndex int, @PageSize int, @RecordCountOut INT SET @SearchText = 'a' SET @SortOrder = 'DESC' SET @ColName= 'LAST_NAME' SET @StartIndex = 1 SET @PageSize = 10 EXEC TestDynProc @SearchText , @SortOrder , @ColName , @StartIndex , @PageSize , @RecordCountOut OUT SELECT @RecordCountOut Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Sharpcnet, if this helps please login to Mark As Answer. \| Alert Moderator

-- Working Sample Procedure for above requirement..


CREATE PROCEDURE TestDynProc (
@SearchText nvarchar(50),  @SortOrder nchar(10),  @ColName nvarchar(20),  @StartIndex int,  @PageSize int, @RecordCountOut INT OUT)
AS
BEGIN
 DECLARE @Query nvarchar(max) , @param NVARCHAR(MAX)  = '@RecordCount INT OUT', @RecordCount INT
  
  
    IF @SearchText = ''  
  
    SET @SearchText = null  
  
    ELSE  
  
    SET @SearchText = '''%'+@SearchText+'%'''  
  
    SET @Query = ' ;WITH TBL AS  
  
                  (  
  
                   SELECT * FROM tblEmployee
  
                   WHERE ('+@ColName+' LIKE '+@SearchText+' OR '+@SearchText+' IS NULL) AND DELETED = 0
                   )   
                  SELECT ROW_NUMBER() OVER(ORDER BY '+@ColName+' '+@SortOrder+' )Row, * INTO #Result FROM TBL 
    
                  SELECT @RecordCount = @@ROWCOUNT ;  
  
                  SELECT * FROM #Result Where Row BETWEEN '+CAST(@StartIndex as VARCHAR) + ' AND ' + CAST(@PageSize as VARCHAR) + '; 
                   DROP TABLE #Result '             
	EXECUTE sp_executesql @query, @Param, @RecordCount OUT 
	SELECT @RecordCountOut = @RecordCount;
END
GO

--Test Procedure execution
DECLARE @SearchText nvarchar(50),  @SortOrder nchar(10),  @ColName nvarchar(20),  @StartIndex int,  @PageSize int, @RecordCountOut INT 
SET @SearchText = 'a'
SET  @SortOrder = 'DESC'
SET  @ColName= 'LAST_NAME'
SET  @StartIndex = 1
SET  @PageSize = 10
EXEC TestDynProc @SearchText ,  @SortOrder ,  @ColName ,  @StartIndex ,  @PageSize , @RecordCountOut OUT
SELECT @RecordCountOut

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sharpcnet, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts