Stored Procedure With Output parameter [Resolved]

Posted by Sharpcnet under Sql Server on 11/30/2013 | Points: 10 | Views : 3045 | Status : [Member] | Replies : 6

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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'

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

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

0	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

0	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

0	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

Latest Posts