CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Kamran Ahmed
Kamran Ahmed

Reputation: 12438

Sql Server 2008: Strange error in stored procedure

I am trying to run the following sql server stored procedure, and I am unable to get it to execute. The procedure query is:

ALTER PROCEDURE [dbo].[get_StockNavigationReportData]
    @startDate VARCHAR(200),
    @endDate VARCHAR(200),
    @groupBy VARCHAR(200)
AS
BEGIN
    SELECT M.VRNOA, M.VRDATE 'DATE', M.REMARKS, D.qty 'QTY', g2.name 'Godown_2', g.name 'Godown_1'
    FROM 
        Stockmain M, StockDetail D, GODOWN G,   (
            SELECT * FROM GODOWN
        ) AS g2
    WHERE 
            M.Etype='navigation' 
        AND M.STID = D.STID
        AND D.GODOWN_ID = G.GODOWN_ID 
        AND g2.godown_id = D.GODOWN_ID2
        AND VRDATE  BETWEEN CONVERT(VARCHAR, CAST(@startDate AS DATETIME),101)
        AND CONVERT(VARCHAR, CAST(@endDate AS DATETIME), 101)   
    ORDER BY 
        @groupBy ASC
END

And the error that I get is:

Msg 1008, Level 16, State 1, Procedure get_StockNavigationReportData, Line 25
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.

Can anybody please have a look and tell me what I am doing wrong here + How may I get this to work?

Upvotes: 0

Views: 2036

Answers (3)

L_7337
L_7337

Reputation: 2748

You can't order by a variable that way.

If you want to do that, build a dynamic query string, then execute it.

Something like:

declare @sql varchar(4000)

set @sql = 'select * from tablename order by ' + @orderbyclause + ' ASC'
exec (@sql)

Upvotes: 2

Ritesh kumar
Ritesh kumar

Reputation: 278

yes you cannot use variable in the order by clause which specify order position. http://exacthelp.blogspot.com/2012/03/how-to-use-variable-in-order-by-clause.html

You should write something like this: 

ORDER BY 
        CASE 
WHEN @groupBy = 1 THEN VRNOA
WHEN @groupBy = 2 THEN DATE
WHEN @groupBy = 3 THEN REMARKS
...............
............
END

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You cannot use a variable in the order by clause. Instead, you have to do something like:

order by (case when @groupBy = 'VRNOA' then VRNOA
               . . .
          end)

Be careful, though, because if the columns are of different types, either unexpected things might happen or might get another error. (There is an alternative to use dynamic SQL, but I would not recommend that.)

Also, the name @groupby is a bit misleading. "Grouping" is a SQL term equivalent to "aggregation". Wouldn't @OrderBy or @SortBy be more appropriate?

Upvotes: 5

Related Questions