为什么这样写，存储过程会错

[anywherenet]

/*============================================================================
var_Order: 排序字段
var_Desc 升降（asc 和 desc）
============================================================================*/
CREATE PROCEDURE P_QR(@var_Order varchar(20),@var_Desc varchar(12))
--WITH ENCRYPTION
AS
begin

begin transaction
--save transaction savepoint_name

select bmid,bmmc,bmxx,bmkh from bm c where and zt = 6 order by @var_Order @var_Desc

end
GO

错误提示：'@var_Desc' 附近有语法错误。

[liuqinyi]

select bmid,bmmc,bmxx,bmkh from bm c where and zt = 6 order by @var_Order ,@var_Desc
需要加上逗号

[qiao198]

select bmid,bmmc,bmxx,bmkh from bm c where (zt = 6) order by @var_Order ,@var_Desc

[liuqinyi]

不好意思，我错了，这是正确的。
CREATE PROCEDURE P_QR(@var_Order varchar(20),@var_Desc varchar(12))
--WITH ENCRYPTION
AS
begin
declare @a varchar(20)
declare @b varchar(12)
select @a=@var_Order
select @b=@var_desc
begin transaction
--save transaction savepoint_name

select bmid,bmmc,bmxx,bmkh from bm c where and zt = 6 order by @var_Order @var_Desc

end
GO

[liuqinyi]

不好意思，今天晕了。
CREATE PROCEDURE P_QR(@var_Order varchar(20),@var_Desc varchar(12))
--WITH ENCRYPTION
AS
begin
declare @a varchar(20)
declare @b varchar(12)
select @a=@var_Order
select @b=@var_desc
begin transaction
--save transaction savepoint_name

select bmid,bmmc,bmxx,bmkh from bm c where and zt = 6 order by @a @b

end
GO

[anywherenet]

还是有错啊

[bladeinside]

exec ('bmid,bmmc,bmxx,bmkh from bm c where and zt = 6 order by ' + @var_Order + @var_Desc )

[anywherenet]

没有人吗？

[anywherenet]

up

[tigerwen01]

当然有错，改一下：
select bmid,bmmc,bmxx,bmkh from bm,c where zt = 6 order by @var_Order,@var_Desc

[sicainiao]

所需排序的之间要用逗号隔开。

[anywherenet]

还是不行。
ORDER BY 子句所标识的第 1 个 SELECT 项包含了一个变量，该变量位于标识列位置的表达式中。只有排序依据表达式引用的是列名时，才允许在该表达式中使用变量。

[forestyang]

select bmid,bmmc,bmxx,bmkh from bm c（bm c 之间为什么有空格？） where (没有条件？） and zt = 6 order by @var_Order @var_Desc

这句的问题：
select bmid,bmmc,bmxx,bmkh from bmc where zt = 6 order by @var_Order @var_Desc

[forestyang]

上面还是错的，改成：

exec ('select bmid,bmmc,bmxx,bmkh from bmc where zt = 6 order by '+ @var_Order + ' ' + @var_Desc)

可以运行

[forestyang]

如果我之前指出的错误不是错误的话，下面这个是正确地(否则将exec那句改成上面答复的形式）：

CREATE PROCEDURE P_QR(@var_Order varchar(20),@var_Desc varchar(12))
--WITH ENCRYPTION
AS
begin

begin transaction
--save transaction savepoint_name

exec ('select bmid,bmmc,bmxx,bmkh from bm c where and zt = 6 order by '+ @var_Order + ' ' + @var_Desc)

commit
end
GO