Tuesday, December 24, 2013

Handle Pagination in Stored Procedure

On one of my project I had requirement from team to write a stored proc that take page number and returns results accordingly.It was fun to develop that logic.
I am sharing it for readers to help them writing similar code. following code is developed using MSSQL 2005 .
I hope this will help others.

Table Structure.
-----------------
CREATE TABLE [dbo].[tblQuantityPricing](
    [QuantityPricingId] [int] IDENTITY(1,1) CONSTRAINT [PK_tblQuantityPricing] PRIMARY KEY CLUSTERED ,
    [QuantityFrom] [int] NULL,
    [QuantityTo] [int] NULL,
    [Price] [decimal](16, 4) NULL,
    [ItemId] [int] NULL

);

--Sample DATA loading
-----------
INSERT INTO tblQuantityPricing(QuantityFrom,QuantityTo,Price,ItemId)
SELECT 10,20,12.5,123
UNION ALL
SELECT 10,20,12.5,1234
UNION ALL
SELECT 10,20,12.5,1235
UNION ALL
SELECT 10,20,12.5,1231
UNION ALL
SELECT 10,20,12.5,1234
UNION ALL
SELECT 10,20,12.5,1234
UNION ALL
SELECT 10,20,12.5,1235
UNION ALL
SELECT 10,20,12.5,1235
UNION ALL
SELECT 10,20,12.5,1236
UNION ALL
SELECT 10,20,12.5,1236
UNION ALL
SELECT 10,20,12.5,1236
UNION ALL
SELECT 10,20,12.5,1236
UNION ALL
SELECT 10,20,12.5,1238
UNION ALL
SELECT 10,20,12.5,122;


Stored Proc

------------

CREATE PROCEDURE spQuantityPricingByPageGet
    (
      @PageNum TINYINT ,
      @TotalRows INT OUTPUT
    )
AS
    BEGIN
        DECLARE @PageSize INT ;
-- pages size= rows per page. Currently fixed as 10 rows/page. You can set it accordingly or can use input parameter to let application decide
        SET @PageSize = 5 ;
   
-- Page wise result fetch
        WITH    QPrice
                  AS ( SELECT   qp.QuantityPricingId ,
                                qp.itemId ,
                                qp.QuantityFrom ,
                                qp.QuantityTo ,
                                qp.Price,
                                ROW_NUMBER() OVER ( ORDER BY ItemID ASC ) AS RowIndex
                       FROM     dbo.tblQuantityPricing qp
                    
                     )
            SELECT  *
            FROM    Qprice
            WHERE   Qprice.RowIndex BETWEEN ( @PageNum - 1 ) * @PageSize + 1
                                    AND     @PageNum * @PageSize ;
                      
-- Counting Total Rows                                 
        SELECT  @TotalRows = COUNT(*)
        FROM    dbo.tblQuantityPricing;

  

    END ; 

-- Sample Execution
DECLARE @totalRows INT;
EXECUTE spQuantityPricingByPageGet 2,@TotalRows = @TotalRows OUTPUT;
SELECT    @TotalRows as N'@TotalRows'

-- Drop Proc
--DROP PROCEDURE  spQuantityPricingByPageGet
  
--Drop table
--DROP TABLE tblQuantityPricing

Thursday, December 12, 2013

Parametrized OpenQuery

While working on an ETl routine, I need to pass parameter to OpenQuery sql.

I searched and found on most of the place that it can be done only with dynamic sql. I want to avoid dynamic sql.
atlast I found Jeff Moden post on sqlservercentral.com. I am posting it here with the intentions that it may help more peoples and  may come up in search engine results.
Thanks to Jeff Moden.


select <stuff ...>
from OPENQUERY(Banner_Prod, 'SELECT a.Major
,a.FIRSTNAME AS NameFirst
,a.MIDDLENAME AS NameMiddle
,a.LASTNAME AS NameLast
,a.CLASSCODE AS PersonSubType
FROM fredtrailer.student_Directory_info a'
) oq
WHERE oq.STUD_ID = @STUD_ID