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:
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:
DECLARE @SortVar tinyint
SET @SortVar = 2
SELECT column1, column2, column3
FROM table
ORDER BY CASE WHEN @SortVar = 1 THEN column1
WHEN @SortVar = 2 THEN column2
ELSE column3
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.
Discussion
No comments yet.