8
\$\begingroup\$

Is there a better way (I'm pretty sure there is) to create a stored procedure to do dynamic paging, filtering and sorting than the following code?

I'm actually generating this sproc from a database using a T4 template.

CREATE PROCEDURE [uspProductWithPagingSortingFiltering]
(
-- Optional Filters for Dynamic Search
@ProductID INT = NULL,
@Name NAME = NULL,
@ProductNumber NVARCHAR(25) = NULL,
@Color NVARCHAR(15) = NULL,
@StandardCost MONEY = NULL,
@ListPrice MONEY = NULL,
@Size NVARCHAR(5) = NULL,
@Weight DECIMAL = NULL,
@ProductCategoryID INT = NULL,
@ProductModelID INT = NULL,
@SellStartDate DATETIME = NULL,
@SellEndDate DATETIME = NULL,
@DiscontinuedDate DATETIME = NULL,
@ThumbNailPhoto VARBINARY = NULL,
@ThumbnailPhotoFileName NVARCHAR(50) = NULL,
@rowguid UNIQUEIDENTIFIER = NULL,
@ModifiedDate DATETIME = NULL,
-- Pagination Parameters
@PageNo INT = 1,
@PageSize INT = 25,
-- Sorting Parameters
@SortColumn NVARCHAR(20),
@SortOrder NVARCHAR(4) = 'ASC'
)
AS
BEGIN
-- Declaring Local Variables corresponding to parameters for modification
DECLARE
@lProductID INT,
@lName NAME,
@lProductNumber NVARCHAR(25),
@lColor NVARCHAR(15),
@lStandardCost MONEY,
@lListPrice MONEY,
@lSize NVARCHAR(5),
@lWeight DECIMAL,
@lProductCategoryID INT,
@lProductModelID INT,
@lSellStartDate DATETIME,
@lSellEndDate DATETIME,
@lDiscontinuedDate DATETIME,
@lThumbNailPhoto VARBINARY,
@lThumbnailPhotoFileName NVARCHAR(50),
@lrowguid UNIQUEIDENTIFIER,
@lModifiedDate DATETIME,

@lPageNbr INT,
@lPageSize INT,
@lSortCol NVARCHAR(20),
@lFirstRec INT,
@lLastRec INT,
@lTotalRows INT,
@TotalCount INT

-- Setting Local Variables
SET @lProductID = @ProductID
SET @lName = @Name
SET @lProductNumber = @ProductNumber
SET @lColor = @Color
SET @lStandardCost = @StandardCost
SET @lListPrice = @ListPrice
SET @lSize = @Size
SET @lWeight = @Weight
SET @lProductCategoryID = @ProductCategoryID
SET @lProductModelID = @ProductModelID
SET @lSellStartDate = @SellStartDate
SET @lSellEndDate = @SellEndDate
SET @lDiscontinuedDate = @DiscontinuedDate
SET @lThumbNailPhoto = @ThumbNailPhoto
SET @lThumbnailPhotoFileName = @ThumbnailPhotoFileName
SET @lrowguid = @rowguid
SET @lModifiedDate = @ModifiedDate

SET @lPageNbr = @PageNo
SET @lPageSize = @PageSize
SET @lSortCol = LTRIM(RTRIM(@SortColumn))

SET @lFirstRec = ( @lPageNbr - 1 ) * @lPageSize
SET @lLastRec = ( @lPageNbr * @lPageSize + 1 )
SET @lTotalRows = @lFirstRec - @lLastRec + 1

; WITH ResultsCTE
AS (
SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN (@lSortCol = 'ProductID' AND @SortOrder='ASC') THEN [ProductID] END ASC,
CASE WHEN (@lSortCol = 'ProductID' AND @SortOrder='DESC') THEN [ProductID] END DESC,
CASE WHEN (@lSortCol = 'Name' AND @SortOrder='ASC') THEN [Name] END ASC,
CASE WHEN (@lSortCol = 'Name' AND @SortOrder='DESC') THEN [Name] END DESC,
CASE WHEN (@lSortCol = 'ProductNumber' AND @SortOrder='ASC') THEN [ProductNumber] END ASC,
CASE WHEN (@lSortCol = 'ProductNumber' AND @SortOrder='DESC') THEN [ProductNumber] END DESC,
CASE WHEN (@lSortCol = 'Color' AND @SortOrder='ASC') THEN [Color] END ASC,
CASE WHEN (@lSortCol = 'Color' AND @SortOrder='DESC') THEN [Color] END DESC,
CASE WHEN (@lSortCol = 'StandardCost' AND @SortOrder='ASC') THEN [StandardCost] END ASC,
CASE WHEN (@lSortCol = 'StandardCost' AND @SortOrder='DESC') THEN [StandardCost] END DESC,
CASE WHEN (@lSortCol = 'ListPrice' AND @SortOrder='ASC') THEN [ListPrice] END ASC,
CASE WHEN (@lSortCol = 'ListPrice' AND @SortOrder='DESC') THEN [ListPrice] END DESC,
CASE WHEN (@lSortCol = 'Size' AND @SortOrder='ASC') THEN [Size] END ASC,
CASE WHEN (@lSortCol = 'Size' AND @SortOrder='DESC') THEN [Size] END DESC,
CASE WHEN (@lSortCol = 'Weight' AND @SortOrder='ASC') THEN [Weight] END ASC,
CASE WHEN (@lSortCol = 'Weight' AND @SortOrder='DESC') THEN [Weight] END DESC,
CASE WHEN (@lSortCol = 'ProductCategoryID' AND @SortOrder='ASC') THEN [ProductCategoryID] END ASC,
CASE WHEN (@lSortCol = 'ProductCategoryID' AND @SortOrder='DESC') THEN [ProductCategoryID] END DESC,
CASE WHEN (@lSortCol = 'ProductModelID' AND @SortOrder='ASC') THEN [ProductModelID] END ASC,
CASE WHEN (@lSortCol = 'ProductModelID' AND @SortOrder='DESC') THEN [ProductModelID] END DESC,
CASE WHEN (@lSortCol = 'SellStartDate' AND @SortOrder='ASC') THEN [SellStartDate] END ASC,
CASE WHEN (@lSortCol = 'SellStartDate' AND @SortOrder='DESC') THEN [SellStartDate] END DESC,
CASE WHEN (@lSortCol = 'SellEndDate' AND @SortOrder='ASC') THEN [SellEndDate] END ASC,
CASE WHEN (@lSortCol = 'SellEndDate' AND @SortOrder='DESC') THEN [SellEndDate] END DESC,
CASE WHEN (@lSortCol = 'DiscontinuedDate' AND @SortOrder='ASC') THEN [DiscontinuedDate] END ASC,
CASE WHEN (@lSortCol = 'DiscontinuedDate' AND @SortOrder='DESC') THEN [DiscontinuedDate] END DESC,
CASE WHEN (@lSortCol = 'ThumbNailPhoto' AND @SortOrder='ASC') THEN [ThumbNailPhoto] END ASC,
CASE WHEN (@lSortCol = 'ThumbNailPhoto' AND @SortOrder='DESC') THEN [ThumbNailPhoto] END DESC,
CASE WHEN (@lSortCol = 'ThumbnailPhotoFileName' AND @SortOrder='ASC') THEN [ThumbnailPhotoFileName] END ASC,
CASE WHEN (@lSortCol = 'ThumbnailPhotoFileName' AND @SortOrder='DESC') THEN [ThumbnailPhotoFileName] END DESC,
CASE WHEN (@lSortCol = 'rowguid' AND @SortOrder='ASC') THEN [rowguid] END ASC,
CASE WHEN (@lSortCol = 'rowguid' AND @SortOrder='DESC') THEN [rowguid] END DESC,
CASE WHEN (@lSortCol = 'ModifiedDate' AND @SortOrder='ASC') THEN [ModifiedDate] END ASC,
CASE WHEN (@lSortCol = 'ModifiedDate' AND @SortOrder='DESC') THEN [ModifiedDate] END DESC
) AS ROWNUM,
Count(*) over () AS TotalCount,
[ProductID],
[Name],
[ProductNumber],
[Color],
[StandardCost],
[ListPrice],
[Size],
[Weight],
[ProductCategoryID],
[ProductModelID],
[SellStartDate],
[SellEndDate],
[DiscontinuedDate],
[ThumbNailPhoto],
[ThumbnailPhotoFileName],
[rowguid],
[ModifiedDate]
FROM SalesLT.Product
WHERE
(@lProductID IS NULL OR [ProductID] = @lProductID)
AND
(@lName IS NULL OR [Name] like '%' + @lName + '%')
AND
(@lProductNumber IS NULL OR [ProductNumber] like '%' + @lProductNumber + '%')
AND
(@lColor IS NULL OR [Color] like '%' + @lColor + '%')
AND
(@lStandardCost IS NULL OR [StandardCost] = @lStandardCost)
AND
(@lListPrice IS NULL OR [ListPrice] = @lListPrice)
AND
(@lSize IS NULL OR [Size] like '%' + @lSize + '%')
AND
(@lWeight IS NULL OR [Weight] = @lWeight)
AND
(@lProductCategoryID IS NULL OR [ProductCategoryID] = @lProductCategoryID)
AND
(@lProductModelID IS NULL OR [ProductModelID] = @lProductModelID)
AND
(@lSellStartDate IS NULL OR [SellStartDate] = @lSellStartDate)
AND
(@lSellEndDate IS NULL OR [SellEndDate] = @lSellEndDate)
AND
(@lDiscontinuedDate IS NULL OR [DiscontinuedDate] = @lDiscontinuedDate)
AND
(@lThumbNailPhoto IS NULL OR [ThumbNailPhoto] = @lThumbNailPhoto)
AND
(@lThumbnailPhotoFileName IS NULL OR [ThumbnailPhotoFileName] like '%' + @lThumbnailPhotoFileName + '%')
AND
(@lrowguid IS NULL OR [rowguid] = @lrowguid)
AND
(@lModifiedDate IS NULL OR [ModifiedDate] = @lModifiedDate)
)

SELECT * INTO #Results FROM ResultsCTE

SELECT @TotalCount = (SELECT count(*) FROM SalesLT.Product)

SELECT
[ProductID],
[Name],
[ProductNumber],
[Color],
[StandardCost],
[ListPrice],
[Size],
[Weight],
[ProductCategoryID],
[ProductModelID],
[SellStartDate],
[SellEndDate],
[DiscontinuedDate],
[ThumbNailPhoto],
[ThumbnailPhotoFileName],
[rowguid],
[ModifiedDate]
FROM #Results AS Results
WHERE ROWNUM > @lFirstRec AND ROWNUM < @lLastRec
ORDER BY ROWNUM ASC

RETURN @TotalCount

END
GO
\$\endgroup\$
  • \$\begingroup\$ What is the primary key on SalesLT.Product? \$\endgroup\$ – rolfl Jul 8 '14 at 16:48
  • \$\begingroup\$ ProductID is the primary key on SalesLT.Product table. \$\endgroup\$ – Övünç Gürsoy Jul 8 '14 at 16:51
  • \$\begingroup\$ @Ovunc similar to this am having more than 20 columns in our table. had you done any further analysis about dynamic query \$\endgroup\$ – SivaRajini Nov 3 '16 at 11:38
6
\$\begingroup\$

These are really the only local variables that you need

SET @lSortCol = LTRIM(RTRIM(@SortColumn))

SET @lFirstRec = ( @lPageNbr - 1 ) * @lPageSize
SET @lLastRec = ( @lPageNbr * @lPageSize + 1 )
SET @lTotalRows = @lFirstRec - @lLastRec + 1

and you don't really need some of them

SET @SortColumn = LTRIM(RTRIM(@SortColumn))
SET @FirstRec = (@PageNo - 1) * @PageSize
SET @LastRec = (@PageNp * @PageSize + 1)
SET @TotalRows = @FirstRec - @LastRec + 1)

you should just use the Parameters that are passed in.

I am thinking that you can't assign a different value to a parameter, so the values that you need to change will need a new variable, but if you aren't going to change the value just use the parameter.


you should also be consistent in your naming schemes as well. if you use a Capitalized first letter in your variables, do that with all your variables.

| improve this answer | |
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.