• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 159
  • Last Modified:

Paged Results

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
JRockFL
Asked:
JRockFL
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
its better to use dynamic sql rather than putting a big case statement
0
 
JRockFLAuthor Commented:
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
 
appariCommented:
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
 
JRockFLAuthor Commented:
Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now