Click here to Skip to main content
12,301,394 members (59,614 online)
Rate this:
 
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 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 8-Jan-13 2:24am
   
yeah you are Right need some wrk to pass startindex and pagecount based on pageindex.
samu4u 8-Jan-13 2:23am
   
Thanks...
Muralikrishna8811 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 8-Jan-13 2:18am
   
how we can change this query like that !!
Christian Graus 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160525.2 | Last Updated 8 Jan 2013
Copyright © CodeProject, 1999-2016
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