-3

I have a table with 2 columns:

CREATE TABLE test(
id int, 
name nvarchar(50)
)
go 

insert INTO Test(id, name)
VALUES (1, 'aa')
insert INTO Test(id, name)
VALUES (2, 'bb')
insert INTO Test(id, name)
VALUES (3, 'aa')

I need a stored procedure that selects data from my table in different orders.

    CREATE PROCEDURE testSelect(
@OrderColumnID int
)    
AS 
BEGIN
    SELECT *
    FROM test
    ORDER BY CASE @OrderColumnID WHEN 1 THEN 1 WHEN 2 THEN 2 END ASC
END

WHEN 1 THEN 1 WHEN 2 THEN 2 means, that if input param = 1, the table must be ordered by the 'id' column, if input param = 2 table must be ordered by the 'name' column.

When I execute the SP like:

EXEC dbo.testSelect @OrderColumnID = 2

I get an incorrect result.

I don't want to rewrite my SP to use dynamic SQL, or separate it into if ..... else if .... blocks like

CREATE PROCEDURE testSelect(
@OrderColumnID int
)    
AS 
BEGIN
    IF @OrderColumnID = 1
    SELECT *
    FROM test
    ORDER BY 1
    ELSE IF @OrderColumnID = 2 
    SELECT *
    FROM test
    ORDER BY 2
END
4

This query means, if @OrderColumnID = 1 then order by 1 (as int, not as first column). So you get random order.

You can get expected result by:

CREATE PROCEDURE testSelect(
    @OrderColumnID int
)
AS 
BEGIN
SELECT *
FROM test
ORDER BY CASE @OrderColumnID WHEN 1 THEN Id END ASC,
    CASE @OrderColumnID WHEN 2 THEN name END ASC
END

Read more about it here "Specifying a conditional order". The ORDER BY CASE... construction may be able to use indexes to avoid a sort if you add an OPTION (RECOMPILE) on SQL Server 2008 SP1 CU5 or later.

Of course, you can use dynamic SQL. Something like this:

CREATE PROCEDURE testSelect(
    @OrderColumnID int
)
AS 
BEGIN
DECLARE @OrderColumnID INT = 2,
    @sql NVARCHAR(MAX)

SET @sql = 'SELECT * FROM test ORDER BY '+CAST(@OrderColumnID AS VARCHAR(2))

EXEC sp_executesql @sql
END

I think that it is easiest way and I don't know why you don't want to do that (with appropriate protection from SQL injection attacks).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.