How to use ROW_NUMBER() for paging data in procedure

Question

How to use ROW_NUMBER() for paging data in procedure

mohsen.bahrzadeh

SSC Enthusiast

Points: 144
More actions
November 5, 2012 at 12:13 am

#284150

i'm starter in sql server, i write this Query
ALTER PROCEDURE [dbo].[SPSelectReport3] (@StringWhereParameter nvarchar(4000)=null)
AS
BEGIN
SET NOCOUNT ON;
-- َ Begin Of Transaction
begin tran
declare @Query nvarchar(max)
set @Query='
((SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[ST_CODE]
,[PlanCode]
,[Min]
,[Max]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[PartNo]
,[TranQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report3]
WHERE headerid IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report3] WHERE line=''H'''+ @StringWhereParameter+'))
UNION
(
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[ST_CODE]
,[PlanCode]
,[Min]
,[Max]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[PartNo]
,[TranQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report3]
WHERE mesc IN(SELECT mesc FROM [MyMaterialDB].[dbo].[Report3] WHERE line=''I''' +@StringWhereParameter+'))
UNION
(SELECT Id,[Mesc]
,[Line]
,[Unit]
,[Discription]
,[InvQty]
,[LastDateNil]
,[ST_CODE]
,[PlanCode]
,[Min]
,[Max]
,[PbsNo]
,[PbsDate]
,[PbsQty]
,[PbsQtyRec]
,[DateDelay]
,[PartNo]
,[TranQty]
,[TypeRequest]
,[HeaderId]
FROM [MyMaterialDB].[dbo].[Report3]
WHERE mesc IN(SELECT HeaderId FROM [MyMaterialDB].[dbo].[Report3] WHERE line=''I'''+@StringWhereParameter+')
)))
Order by Mesc,Line,unit'
exec(@Query)
if @@error = 0
Commit Tran
Else
rollback tran
End
i write this string Query and Get Where parametrs and concat Query and where after that run Query. i want paging result data but i dont know how to do paging.
please help me. thanks all.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

mohsen.bahrzadeh (11/5/2012)
i want paging result data but i dont know how to do paging.
please help me. thanks all.

What exactly do you want here ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

mohsen.bahrzadeh SSC Enthusiast Points: 144 More actions · Answer 2

This query Returns to many records, i want paging result but i create Query string(' '). i Don't know how to paging this Query. thanks.

Sean Lange SSC Guru Points: 286573 More actions · Answer 3

Eek!!! Your code is wide open to sql injection attack. You need to read up on that immediately. Your code is a textbook example of exactly how NOT use user data in a query.

To answer your question of paging, you need to add a ROW_NUMBER to your query. Then you can pass both pagesize and pagenumber to your query. Do some simple math to determine what range the ROW_NUMBER should be and you are all set.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/