Stored procedure with output parameter

Question

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;

you are assigning int to @TotalRows which is of type nvarchar — Nitin Varpe
– Nitin Varpe, Commented Nov 30, 2013 at 9:46
If assigning to a NVARCHAR variable, you should use the N'...' to indicate Unicode. Next: you must put a semicolon (;) before the with keyword. — marc_s
– marc_s, Commented Nov 30, 2013 at 9:47

Indranil.Bharambe · Accepted Answer · 2013-11-30 10:08:13Z

1

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

edited Nov 30, 2013 at 10:08

answered Nov 30, 2013 at 9:55

Indranil.Bharambe

1,4983 gold badges14 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Ruby Over a year ago

You have declared it as int in paramDefinition. The first declaration was nvarchar. So shold I also change that to int. Why is there an extra comma/param after @query in the exec statement. I'm assuming that the final execute statement to test the procedure is correct...is it

Ruby Over a year ago

execute GetRecordsByPage 'tblBranch','BranchName', '2', '10', /@TotalRows output - gives an error- must declare scalar variable /@TotalRows - But isnt it already declared

Indranil.Bharambe Over a year ago

y are you putting /@totalrows use GetRecordsByPage 'tblBranch','BranchName', '2', '10', @TotalRows

Ruby Over a year ago

When using just '@'...it isnt allowing to submit. It thinks thts a username. The line's just as you wrote, but with 'output at the end

Indranil.Bharambe Over a year ago

how you call it is it c#.. i am just asking

|

Collectives™ on Stack Overflow

Stored procedure with output parameter

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related