case statement in store procedure of sql server 2008

Case statement in the storeprocedure:

Example:

Sort table rows by a specified column in ascending and descending order.

1. Create a Book Table, as in:

CREATE TABLE BOOK

(

     Id int Identity(1,1) primary key,

     Name nvarchar(50),

     Author nvarchar(50),

     Price decimal(18,2)

)

2. Insert data into the table, as in:

INSERT INTO BOOK (Name,Author,Price)

 VALUES ('ASP.NET 3.5 UNLEASHED','Stephen Walther',600),

 ('DATA STRUCTURES', 'SEYMOUR LIPSCHUTZ',300),

 ('jQuery UI','Eric Sarrion',200)

3. All the data for Books looks like:

4. Create a Stored Procedure to sort table rows in ascending or descending order. The default is ascending order of the Name of the books. See:

CREATE PROCEDURE SortBooks

(

     @column nvarchar(50)='Name',

     @order int = 0

)

AS

BEGIN

SET NOCOUNT ON;

SELECT Id,Name,Author,Price FROM BOOK ORDER BY

           CASE

           WHEN @column='Name' AND @order=0 THEN Name END,

           CASE

           WHEN @column='Name' AND @order=1 THEN Name END DESC,

           CASE

           WHEN @column='Author' AND @order=0 THEN Author END,

           CASE

           WHEN @column='Author' AND @order=1 THEN Author END DESC,

           CASE

           WHEN @column='Price' AND @order=0 THEN Price END,

           CASE

           WHEN @column='Price' AND @order=1 THEN Price END DESC                    

END

5. Sort by Author name in ascending order:

declare @column nvarchar(50)= 'Author'

declare @order int = 0

exec SortBooks @column,@order