Ouput parameter from dynamic sql

Posted by Sharpcnet under Sql Server on 2/16/2014 | Points: 10 | Views : 1869 | Status : [Member] | Replies : 5
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





Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
@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

Up
0
Down
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

Up
0
Down
-- 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

Login to post response