Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
Hi,
 

CREATE PROCEDURE [dbo].[spStudentListPagination] 
    
     @start int = 0,
     @pageCount int=0
 
AS
BEGIN
   
     Set NOCOUNT ON
     SELECT TOP @pageCount * FROM 
     (
          SELECT 
       [STNO]
      ,[SNAME]
      ,[MAJOR]
      ,[CLASS]
      ,[BDATE]
      , ROW_NUMBER() OVER (ORDER BY STNO) AS num
      FROM [student_db].[dbo].[Student]
     ) AS a
     WHERE num > @start
END
 

it generating the error
 

Msg 102, Level 15, State 1, Procedure spStudentListPagination, Line 9
Incorrect syntax near '@pageCount'.
Msg 156, Level 15, State 1, Procedure spStudentListPagination, Line 19
Incorrect syntax near the keyword 'AS'.
 

when i am giving the direct value to @start and @pagecount The result is selecting the table data properly..
Posted 7-Jan-13 19:59pm
samu4u1K
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hi Christian,
 
Try it out .
 
  CREATE PROCEDURE GetBranchesnyPage 
    
     @start int = 0,
     @pageCount int=0
 
AS
BEGIN
   
    
     Set NOCOUNT ON
     SELECT  * FROM 
     (
          SELECT 
       [Id]
      ,[BranchName]
      ,[CountryCode] 
      , ROW_NUMBER() OVER (ORDER BY Id) AS num
      FROM Branches
     ) AS A
     WHERE num > @start and num <=(@start+@pageCount)
END
 

I hope it can help you
 
All the best
  Permalink  
Comments
Christian Graus at 8-Jan-13 2:19am
   
I did not ask the question, but this looks to me like it will sort of work. @start is the start index, not a page number.
Muralikrishna8811 at 8-Jan-13 2:24am
   
yeah you are Right need some wrk to pass startindex and pagecount based on pageindex.
samu4u at 8-Jan-13 2:23am
   
Thanks...
Muralikrishna8811 at 8-Jan-13 2:26am
   
Very welcome
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

SQL Server does not support this. Sadly, you need to create a line of SQL and use EXEC, to do this.
  Permalink  
Comments
samu4u at 8-Jan-13 2:18am
   
how we can change this query like that !!
Christian Graus at 8-Jan-13 2:20am
   
What the other guy posted, is better.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web04 | 2.8.141022.2 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100