CODEWITHSUNDEEP
sql-serverstored-proceduressql-order-by
Luca Romagnoli
Luca Romagnoli

Reputation: 12455

order by a parameter

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

Upvotes: 11

Views: 6051

Answers (3)

ajaxer
ajaxer

Reputation: 136

Another option is to use an expression for the column that you want to sort by.

DECLARE @OrderBy INT

SET @OrderBy = 4

SELECT     *
FROM         MySourceTable
ORDER BY COL_NAME(OBJECT_ID('MySourceTable'), @OrderBy )

It is usually better to avoid dynamic sql if you can.

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166326

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

Upvotes: 9

Klaus Byskov Pedersen
Klaus Byskov Pedersen

Reputation: 120917

You are going to have use string concatenation and sp_executesql.

Upvotes: -1

Related Questions