1
\$\begingroup\$

Below stored procedure implemented for filtering and sorting operation.But number of table column increases below query becomes complex to read/maintain. This is good for small tables.

I want to modify the below one by using dynamic sql query. I will pass the parameter from front-end to here.

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

How can i modify the above stored procedure in a better manner when number of column increases by using dynamic sql queries?

\$\endgroup\$

1 Answer 1

Reset to default
1
\$\begingroup\$

Dynamic Sorting

You could reduce half of the CASE statements by removing the explict ASC/DESC sorting.

For example, the CASE statements would turn into:

SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN (@lSortCol = 'ProductID') THEN [ProductID] END,
CASE WHEN (@lSortCol = 'Name') THEN [Name] END,
CASE WHEN (@lSortCol = 'ProductNumber') THEN [ProductNumber] END,
CASE WHEN (@lSortCol = 'Color') THEN [Color] END,
CASE WHEN (@lSortCol = 'StandardCost') THEN [StandardCost] END,
CASE WHEN (@lSortCol = 'ListPrice') THEN [ListPrice] END,
CASE WHEN (@lSortCol = 'Size') THEN [Size] END,
CASE WHEN (@lSortCol = 'Weight') THEN [Weight] END,
CASE WHEN (@lSortCol = 'ProductCategoryID') THEN [ProductCategoryID] END,
CASE WHEN (@lSortCol = 'ProductModelID') THEN [ProductModelID] END,
CASE WHEN (@lSortCol = 'SellStartDate') THEN [SellStartDate] END,
CASE WHEN (@lSortCol = 'SellEndDate') THEN [SellEndDate] END,
CASE WHEN (@lSortCol = 'DiscontinuedDate') THEN [DiscontinuedDate] END,
CASE WHEN (@lSortCol = 'ThumbNailPhoto') THEN [ThumbNailPhoto] END,
CASE WHEN (@lSortCol = 'ThumbnailPhotoFileName') THEN [ThumbnailPhotoFileName] END,
CASE WHEN (@lSortCol = 'rowguid') THEN [rowguid] END,
CASE WHEN (@lSortCol = 'ModifiedDate') THEN [ModifiedDate] END
) AS ROWNUM

Obviously this doesn't provide a sort direction. So to do this, we just include a new variable that acts as a multiple dependent upon your @SortOrder variable.

DECLARE @SortMultiplier INT = CASE WHEN @SortOrder = 'ASC' THEN 1 ELSE -1 END

Finally, when you actually perform the ORDER BY. We can just multiply the value returned by ROW_NUMBER and the multiplier:

ORDER BY ROWNUM * @SortMultiplier

Therefore, if it's ASC order, the row number won't change. However, if its DESC order, the numbers will be inverted to there negative counterparts. Essentially providing you with a descending order.

The final solution looking as follows:

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,
@SortMultiplier 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

SET @SortMultiplier = CASE WHEN @SortOrder = 'ASC' THEN 1 ELSE -1 END

; WITH ResultsCTE
AS (
SELECT ROW_NUMBER() OVER (ORDER BY
CASE WHEN (@lSortCol = 'ProductID') THEN [ProductID] END,
CASE WHEN (@lSortCol = 'Name') THEN [Name] END,
CASE WHEN (@lSortCol = 'ProductNumber') THEN [ProductNumber] END,
CASE WHEN (@lSortCol = 'Color') THEN [Color] END,
CASE WHEN (@lSortCol = 'StandardCost') THEN [StandardCost] END,
CASE WHEN (@lSortCol = 'ListPrice') THEN [ListPrice] END,
CASE WHEN (@lSortCol = 'Size') THEN [Size] END,
CASE WHEN (@lSortCol = 'Weight') THEN [Weight] END,
CASE WHEN (@lSortCol = 'ProductCategoryID') THEN [ProductCategoryID] END,
CASE WHEN (@lSortCol = 'ProductModelID') THEN [ProductModelID] END,
CASE WHEN (@lSortCol = 'SellStartDate') THEN [SellStartDate] END,
CASE WHEN (@lSortCol = 'SellEndDate') THEN [SellEndDate] END,
CASE WHEN (@lSortCol = 'DiscontinuedDate') THEN [DiscontinuedDate] END,
CASE WHEN (@lSortCol = 'ThumbNailPhoto') THEN [ThumbNailPhoto] END,
CASE WHEN (@lSortCol = 'ThumbnailPhotoFileName') THEN [ThumbnailPhotoFileName] END,
CASE WHEN (@lSortCol = 'rowguid') THEN [rowguid] END,
CASE WHEN (@lSortCol = 'ModifiedDate') THEN [ModifiedDate] END
) 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 * @SortMultiplier

RETURN @TotalCount

END
GO
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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