Dynamic ORDER BY Clause in SQL Server Stored Procedure

Have you ever needed the ability to pass in your sort variable to a SQL Server stored procedure?  At first thought you would think you could do the following:

<code lang="tsql[lines]">ORDER BY @orderVar

But, no dice.  SQL Server will not allow you to set the ORDER BY clause with a variable.  There are two solutions to get around this.  The first is to use a CASE statement to perform the ORDER BY clause:

<code lang="tsql[lines]">DECLARE @SortVar tinyint<br></br>  
    SET @SortVar = 2<br></br>
    SELECT column1, column2, column3<br></br>
    FROM table<br></br>
    ORDER BY CASE WHEN @SortVar = 1 THEN column1<br></br>
    WHEN @SortVar = 2 THEN column2<br></br>
    ELSE column3<br></br>
    END```

The second is make your entire query dynamic, by declaring a local variable that will hold your SQL statement:

DECLARE @checkPaid bit,


@SQLStatement varchar(500)

IF @IsAll = 0

SET @checkPaid = 1

ELSE

SET @checkPaid = 0```

SET @SQLStatement = 'Select d.* From [Items] d WHERE 1 = 1 AND Paid = ' + CAST(@checkPaid as VARCHAR) + Char(10) + CHAR(9)
IF Len(@category) > 0
SET @SQLStatement = @SQLStatement + ' AND category = ''' + @category + '''' + Char(10) + CHAR(9)

IF Len(@size) > 0 AND @size ''
SET @SQLStatement = @SQLStatement + ' AND Size = ' + CAST(@size as VARCHAR) + Char(10) + CHAR(9)

IF Len(@qty) > 0 AND @qty ''
BEGIN
IF @qty = 1
SET @SQLStatement = @SQLStatement + ' AND qty = 1 ' + Char(10) + CHAR(9)
ELSE
SET @SQLStatement = @SQLStatement + ' AND qty > 1 ' + Char(10) + CHAR(9)
END

SET @SQLStatement = @SQLStatement + ' ORDER BY ' + @sort
-- Execute the SQL statement
EXEC(@SQLStatement)

I prefer the second method, as you can also make your entire statement dynamic, for example using IF statements to perform different WHERE clauses.

Comments

comments powered by Disqus