How to use ROW_NUMBER() for paging data in procedure

Question

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.

@ Buzz : in Proc use in asp.net Application, in Client create dynamic where and convert this to string and send to server . — mohsen bh, Nov 05 '12 at 06:11
i m not getting ur point here,but for paging look into these questions-http://stackoverflow.com/questions/4358253/sql-server-2008-paging-methods; http://stackoverflow.com/questions/548475/efficient-way-to-implement-paging — Buzz, Nov 05 '12 at 06:39
@ Buzz: thanks for help me. but this link no help me.in this Query i have 3 query and union it, bur i don't know how to paging query within union — mohsen bh, Nov 05 '12 at 06:55

score 1 · Answer 1 · answered Nov 05 '12 at 07:31

There should be no problem in using the entire thing (minus ORDER BY) as a derived table and applying ROW_NUMBER() in the outer query (and then filtering by the assigned row numbers in the outermost query). But I would probably rewrite this query first, perhaps like this:

'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] t
WHERE EXISTS (
  SELECT *
  FROM [MyMaterialDB].[dbo].[Report3]
  WHERE (Line = ''H'' AND HeaderId = t.HeaderId
      OR Line = ''I'' AND t.Mesc IN (Mesc, HeaderId)
  ) ' + @StringWhereParameter + '
)'

Now it should be easy to add a row number column:

'SELECT Id,[Mesc]
      ,[Line]
      ,[Unit]
      ,[Discription]
      ,[InvQty]
      ,[LastDateNil]
      ,[ST_CODE]
      ,[PlanCode]
      ,[Min]
      ,[Max]
      ,[PbsNo]
      ,[PbsDate]
      ,[PbsQty]
      ,[PbsQtyRec]
      ,[DateDelay]
      ,[PartNo]
      ,[TranQty]
      ,[TypeRequest]
      ,[HeaderId]
      ,ROW_NUMBER() OVER (ORDER BY  Mesc, Line, Unit) AS rn
FROM [MyMaterialDB].[dbo].[Report3] t
WHERE EXISTS (
  SELECT *
  FROM [MyMaterialDB].[dbo].[Report3]
  WHERE (Line = ''H'' AND HeaderId = t.HeaderId
      OR Line = ''I'' AND t.Mesc IN (Mesc, HeaderId)
  ) ' + @StringWhereParameter + '
)'

and then filter on it

'SELECT Id,[Mesc]
      ,[Line]
      ,[Unit]
      ,[Discription]
      ,[InvQty]
      ,[LastDateNil]
      ,[ST_CODE]
      ,[PlanCode]
      ,[Min]
      ,[Max]
      ,[PbsNo]
      ,[PbsDate]
      ,[PbsQty]
      ,[PbsQtyRec]
      ,[DateDelay]
      ,[PartNo]
      ,[TranQty]
      ,[TypeRequest]
      ,[HeaderId]
FROM (
      SELECT Id,[Mesc]
            ,[Line]
            ,[Unit]
            ,[Discription]
            ,[InvQty]
            ,[LastDateNil]
            ,[ST_CODE]
            ,[PlanCode]
            ,[Min]
            ,[Max]
            ,[PbsNo]
            ,[PbsDate]
            ,[PbsQty]
            ,[PbsQtyRec]
            ,[DateDelay]
            ,[PartNo]
            ,[TranQty]
            ,[TypeRequest]
            ,[HeaderId]
            ,ROW_NUMBER() OVER (ORDER BY  Mesc, Line, Unit) AS rn
      FROM [MyMaterialDB].[dbo].[Report3] t
      WHERE EXISTS (
        SELECT *
        FROM [MyMaterialDB].[dbo].[Report3]
        WHERE (Line = ''H'' AND HeaderId = t.HeaderId
            OR Line = ''I'' AND t.Mesc IN (Mesc, HeaderId)
        ) ' + @StringWhereParameter + '
      )
) s
WHERE rn BETWEEN @offset + 1 AND @offset + @pagesize
;'

How to use ROW_NUMBER() for paging data in procedure

1 Answers1