Dynamically filtering, sorting, and paging using a stored procedure

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 @lDisconti

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.

StackExchange Code Review Q#56453, answer score: 6