Execute the following T-SQL database script in Query Editor demonstrates the use of the CASE function to build a stored procedure for dynamic sorting and filtering. The sort fields are the LastName or FirstName columns based on the @SortField sproc parameter.

USE AdventureWorks

GO

CREATE PROCEDURE dbo.procGetContactInfo

      @SortField CHAR(10),

      @AscOrDesc CHAR(4),

      @Title CHAR(5) = NULL

AS

SET nocount ON

SELECT ContactID, Title, FirstName, LastName, Phone

FROM Person.Contact

WHERE Title =

      CASE WHEN @Title IS NOT NULL THEN @Title 

             ELSE Title

      END

ORDER BY 

CASE @AscOrDesc

     WHEN 'asc' THEN

      CASE @SortField 

                  WHEN 'FirstName' THEN FirstName 

                  WHEN 'LastName' THEN LastName 

       END

END 

ASC,

CASE @AscOrDesc

      WHEN 'desc' THEN

       CASE @SortField 

                  WHEN 'FirstName' THEN FirstName 

                  WHEN 'LastName' THEN LastName 

       END

END

DESC

GO

EXEC dbo.procGetContactInfo 'LastName', 'desc', 'Mr.' 

GO

EXEC dbo.procGetContactInfo 'FirstName', 'asc', 'Ms.' 

GO

EXEC dbo.procGetContactInfo 'LastName', 'asc', 'Sr.' 

GO