Stored Procedure With Output parameter [Resolved]

Posted by Sharpcnet under Sql Server on 11/30/2013 | Points: 10 | Views : 2466 | Status : [Member] | Replies : 6
Trying to create a Stored procedure that will give the required no. of records and also the total no. of records in that table.

The '@Query' part gives result when I execute it separately. So there's no problem with that.
Could Someone please check if the syntax is correct, cause I'm not getting at any output

ALTER PROCEDURE GetRecordsByPage
(
@TableName nvarchar(50),
@ColumnName nvarchar(50),
@OrderByClause nvarchar(50),
@StartIndex nvarchar(50),
@EndIndex nvarchar(50),
@TotalRows nvarchar(50) output
)
AS
BEGIN
DECLARE @Query nvarchar(max)
select @TotalRows = 'select count(*) from '+@TableName+' where deleted=0'
select @Query = 'with temp as (select row_number() over (order by '+
@colname+' '+@OrderByClause+') as row, * from '+@tablename+')
select * from temp where row between '+@startIndex+' and '+@EndIndex

execute sp_executesql @Query, @TotalRows output
END
-- execute GetRecordsByPage 'tblBranch','BranchName', '2', '10'





Responses

Posted by: Bandi on: 11/30/2013 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
DECLARE @Query nvarchar(max)

select @Query = 'with temp as (select row_number() over (order by '+

@colname+' '+@OrderByClause+') as row, * from '+@tablename+')

select * from temp where row between '+@startIndex+' and '+@EndIndex

execute sp_executesql @Query;

DECLARE @TotalCountQry NVARCHAR(1000);
SET @TotalCountQry = 'select count(*) from '+@TableName+' where deleted=0'
EXEC(@TotalCountQry)
END

--execution part
execute GetRecordsByPage 'tblBranch','BranchName', '2', '10'


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: Sheonarayan on: 11/30/2013 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
select @TotalRows = 'select count(*) from '+@TableName+' where deleted=0'


Tweak your above code so that it looks like

'select ' + @TotalRows + ' = count(*) from ' + @TableName + ' where deleted = 0'


Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

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

Posted by: Sharpcnet on: 11/30/2013 [Member] Starter | Points: 25

Up
0
Down
So would it be
select @Query = 'select ' + @TotalRows + ' = count(*) from ' + @TableName + ' where         
deleted=0
with temp as (select row_number() over (order by '+

@colname+' '+@OrderByClause+') as row, * from '+@tablename+')

select * from temp where row between '+@startIndex+' and '+@EndIndex
Not working.

Are these both correct

execute sp_executesql @Query, @TotalRows output
-- execute GetRecordsByPage 'tblBranch','BranchName', '2', '10'


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

Posted by: Sharpcnet on: 11/30/2013 [Member] Starter | Points: 25

Up
0
Down
Heres a trimmed down version, but its not giving any output. I am expecting two result sets
1. All records from table
2. Count Of all records from table

declare @rows nvarchar(50), @query nvarchar(max)
set query = 'select @rows+' = count(*) from tblBranch; select * from tblBranch;'
execute sp_executesql @query


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

Posted by: Bandi on: 11/30/2013 [Member] [MVP] Platinum | Points: 25

Up
0
Down
If you know tableName earlier, you can use as follows:

select @TotalRows  = count(*) from tblBranch where deleted = 0;




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: 12/1/2013 [Member] Starter | Points: 25

Up
0
Down
Thank you. That worked. Its just that I do not have to declare an output parameter in this case.
I realized this from your answer in a similar question.

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

Login to post response