Solved

Paged Results

Posted on 2009-05-19
4
150 Views
Last Modified: 2012-05-07
I'm working with some SQL that uses true paging.
I would like to be able pass in a where clause and an order by to the stored procedure.
How can I implement t?
Can i build dynamic sql and then use sp_executesql ? Or is there another method?
ALTER PROCEDURE IMINVLOC_SQL_Paged
@Where varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
AS
 
BEGIN
	DECLARE @SQL nvarchar(2000)
	DECLARE @Count int
	SELECT @Count = (SELECT COUNT(*) FROM IMINVLOC_SQL)
	
	DECLARE @StartRowIndex int
	SET @StartRowIndex = (@PageIndex * @PageSize) + 1;
	
	WITH Results AS (
		SELECT ROW_NUMBER() OVER (ORDER BY Item_No ASC) AS Row, Item_No, Item_Filler, Std_Cost
		FROM IMINVLOC_SQL
	)
	
	SELECT Item_No, Item_Filler, Std_Cost, @Count As TotalRecords
	FROM Results
	WHERE Row BETWEEN
	@StartRowIndex AND @StartRowIndex + @PageSize - 1
 
END

Open in new window

0
Comment
Question by:JRockFL
  • 2
4 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 24427698
its better to use dynamic sql rather than putting a big case statement
0
 
LVL 8

Author Comment

by:JRockFL
ID: 24427728
thank you for your reply,
will i be able to put this in dynamic sql? I'm not familiar with the "WITH" statement.

WITH Results AS (
            SELECT ROW_NUMBER() OVER (ORDER BY Item_No ASC) AS Row, Item_No, Item_Filler, Std_Cost
            FROM IMINVLOC_SQL
      )
      
      SELECT Item_No, Item_Filler, Std_Cost, @Count As TotalRecords
      FROM Results
      WHERE Row BETWEEN
      @StartRowIndex AND @StartRowIndex + @PageSize - 1
0
 
LVL 39

Accepted Solution

by:
appari earned 400 total points
ID: 24427834
yes you can include with in dynamic sql.
try like this

I didnt include checks on @OrderBy and @Where for zero length or null values.
add the checks within the procedure if you are not validating the input before you call the stored procedure.
ALTER PROCEDURE IMINVLOC_SQL_Paged
@Where varchar(2000) = NULL,
@OrderBy varchar(2000) = NULL,
@PageIndex int,
@PageSize int
AS
 
BEGIN
	DECLARE @SQL nvarchar(8000)
	DECLARE @Count int
	SELECT @Count = (SELECT COUNT(*) FROM IMINVLOC_SQL)
	
	DECLARE @StartRowIndex int
	SET @StartRowIndex = (@PageIndex * @PageSize) + 1;
	
	SET @SQL = ';WITH Results AS (
		SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS Row, Item_No, Item_Filler, Std_Cost
		FROM IMINVLOC_SQL Where ' + @Where + ' )
	SELECT Item_No, Item_Filler, Std_Cost, @Count As TotalRecords
	FROM Results
	WHERE Row BETWEEN ' +  @StartRowIndex + ' AND ' +   @StartRowIndex + @PageSize - 1 ;
 
	Exec(@SQL);
 
END

Open in new window

0
 
LVL 8

Author Closing Comment

by:JRockFL
ID: 31583302
Thank you!
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQl server restarts itself 6 37
What is the best way to calculate hours worked 5 69
Isolation level in SQL server 3 49
Move SQL 2005 Express to Server 2012R2 19 125
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question