Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
 CREATE PROCEDURE GetCustomersPageWise
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
     INTO #Results
      FROM [Customers]
     
      SELECT @RecordCount = COUNT(*)
      FROM #Results
           
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
     
      DROP TABLE #Results
END
GO
i need same answere using common table expression only i am already got output
Posted 20-Sep-12 23:48pm
Edited 21-Sep-12 0:00am
v3
Comments
OriginalGriff at 21-Sep-12 5:53am
   
This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind.
Perhaps if you tell us what you expect to get? And what you actually get?
Use the "Improve question" widget to edit your question and provide better information.
Sandeep Mewara at 21-Sep-12 5:56am
   
This is not a well framed question! We cannot work out what you are trying to do/ask from the post. Please elaborate and be specific.
Use the "Improve question" link to edit your question and provide better information.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Try this:
CREATE PROCEDURE GetCustomersPageWise
      @PageIndex INT = 1
      ,@PageSize INT = 10
      ,@RecordCount INT OUTPUT
AS
BEGIN
      SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY [CustomerID] ASC
      )AS RowNumber
      ,[CustomerID]
      ,[CompanyName]
      ,[ContactName]
     INTO #Results
      FROM [Customers]
     
      SELECT @RecordCount = (SELECT COUNT(*) FROM #Results)--Error
           
      SELECT * FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
     
      DROP TABLE #Results
END
GO
 

 
--Amit
  Permalink  
Comments
Sandeep Mewara at 21-Sep-12 5:57am
   
You replied but how? I mean, how do you know what Op was looking for and what needed a fix? :confused:
_Amy at 21-Sep-12 6:54am
   
As OriginalGriff already asked the OP to modify the question. I was just stepping through the code and I found one error which I solved and posted. That might be creating the problem for OP. I guessed. :)
Sandeep Mewara at 21-Sep-12 7:26am
   
Cool! Have 5! Good one.
_Amy at 21-Sep-12 7:32am
   
Thank you. :)

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

  Print Answers RSS
0 OriginalGriff 6,696
1 Sergey Alexandrovich Kryukov 6,675
2 CPallini 5,315
3 George Jonsson 3,584
4 Gihan Liyanage 2,650


Advertise | Privacy | Mobile
Web01 | 2.8.140921.1 | Last Updated 21 Sep 2012
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