Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Paged Results

Posted on 2009-05-19
4
151 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms sql + top 1 for each customer 3 57
Selection from table2 where criteria for table1 10 49
Restrict result set 1 39
Sql query 107 73
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…

828 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