Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use ROW_NUMBER() for paging data in procedure Expand / Collapse
Author
Message
Posted Monday, November 5, 2012 12:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 2:25 PM
Points: 6, Visits: 12
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.
Post #1380895
Posted Monday, November 5, 2012 12:19 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 12:26 AM
Points: 2,840, Visits: 3,963
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
Post #1380897
Posted Monday, November 5, 2012 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 25, 2013 2:25 PM
Points: 6, Visits: 12
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.
Post #1380898
Posted Monday, November 5, 2012 8:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:44 PM
Points: 13,295, Visits: 12,145
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1381112
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse