CODEWITHSUNDEEP
sql-servert-sqlstored-procedures
Ruby
Ruby

Reputation: 969

Stored procedure with output parameter

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 result.

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 @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    

   execute sp_executesql @Query, @TotalRows output 

END
--is this correct, to test this stored procedure
-- execute GetRecordsByPage 'tblBranch','BranchName', '2', '10'

Otherwise, the queries would be:

select count(*) from tblBranch;
With temp as
(select row_number over(order by name desc) as row, * from tblbranch)
select * from temp where row between 11 and 20;

Upvotes: 0

Views: 432

Answers (1)

Indranil.Bharambe
Indranil.Bharambe

Reputation: 1498

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 @Query = 'select @TotalRowsOut = 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    

    DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@TotalRowsOut int OUTPUT';
       execute sp_executesql @Query,  @ParmDefinition,@TotalRowsOut=@TotalRows output 
select @TotalRows
END

Upvotes: 1

Related Questions