order by a parameter

sql-order-by, sql-server, stored-procedures

Solution

You have 2 options, either use a CASE statement, or use dynamic sql

This would be an example of the CASE statement

DECLARE @Table TABLE(
        Col1 VARCHAR(10),
        Col2 VARCHAR(10)
)

DECLARE @OrderBy VARCHAR(100)

SET @OrderBy = 'Col1'

SELECT  *
FROM    @Table
ORDER BY 
        CASE
            WHEN @OrderBy = 'Col1' THEN Col1
            WHEN @OrderBy = 'Col2' THEN Col2
            ELSE Col1
        END

And this would be and example of dynamic sql

CREATE TABLE #Table (
        Col1 VARCHAR(10),
        Col2 VARCHAR(10)
)

DECLARE @OrderBy VARCHAR(100)

SET @OrderBy = 'Col1'

DECLARE @SqlString NVARCHAR(MAX)

SELECT @SqlString = 'SELECT * FROM #Table ORDER BY ' + @OrderBy

EXEC(@Sqlstring)

DROP TABLE #Table

Problem

Hi i have a store procedure, where i do a select query. I want order this by an external parameter. I post an minimal example: ``` CREATE PROCEDURE [dbo].[up_missioni_get_data] @order VarChar(100) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT * from missioni ORDER BY ... END ``` What can i write in order by for do that? thanks

Original source