Sql Server 2008:存储过程中的奇怪错误 [英] Sql Server 2008: Strange error in stored procedure

问题描述

我正在尝试运行以下 sql server 存储过程，但无法执行.程序查询是:

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

我得到的错误是:

消息 1008，级别 16，状态 1，过程 get_StockNavigationReportData，第 25 行
由 ORDER BY 编号 1 标识的 SELECT 项包含一个变量，作为标识列位置的表达式的一部分.仅当按引用列名的表达式排序时才允许使用变量.

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?

推荐答案

是的，您不能在指定订单位置的 order by 子句中使用变量.http://exacthelp.blogspot.com/2012/03/how-to-use-variable-in-order-by-clause.html

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

你应该这样写:

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

这篇关于Sql Server 2008:存储过程中的奇怪错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！