存储过程高手请进，一旦解决，马上给分

[Fusuli]

问题也不难，就是一个存储过程，有两个参数，一个是需要排序的列号，另一个是排序的次序标志：
PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
begin
if (@order = 1)
begin
select * from Tenancy_Room
order by id --按id排序
end
else if(@order = 2)
begin
select * from Tenancy_Room
order by price --按价格排序
end
else
begin
select * from Tenancy_Room
end

end

但是排序的asc和desc怎么加呢，除了if语句嵌套以外还有什么好办法？能不能用一个select就搞定

[txlicenhe]

1: asc desc加在 order by id 后面
PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
begin
if (@order = 1)
begin
select * from Tenancy_Room
order by id desc
end
else if(@order = 2)
begin
select * from Tenancy_Room
order by price asc
end
else
begin
select * from Tenancy_Room
end

end

[pengdali]

PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
begin


select * from Tenancy_Room order by case @order when 1 then id when 2 then price else 1 end

[txlicenhe]

if @order = 1 and @asc = 1 then
select * from Tenancy_Room order by id
else if @order = 1 and @asc = 2 then
select * from Tenancy_Room order by id desc
else if @order = 2 and @asc = 1 then
select * from Tenancy_Room order by price
else if @order = 2 and @asc = 2 then
select * from Tenancy_Room order by price desc
else
select * from Tenancy_Room
end

[pengdali]

PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
begin

declare @a varchar(2000)
set @a='select * from Tenancy_Room order by '+case @order when 1 then 'id' when 2 then 'price' else '' end+case @asc when 1 then ' desc' else '' end
exec(@a)

[yujohny]

create PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
begin
declare @sql nvarchar(4000),@pxsql nvarchar(10)
if @asc=1 set @pxsql=' asc' else set @pxsql=' desc'
if (@order = 1)
set @sql='select * from Tenancy_Room order by id'
else if(@order = 2)
set @sql='select * from Tenancy_Room order by price'
else
set @sql='select * from Tenancy_Room'

set @sql=@sql+@pxsql

exec(@sql)

end

[pengdali]

打个补丁：


PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
begin

declare @a varchar(2000)
set @a='select * from Tenancy_Room '+case @order when 1 then 'order by id' when 2 then 'order by price' else '' end+case when @order in (1,2) and @asc=1 then ' desc' else '' end
exec(@a)

[arrow_gx]

PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
begin

declare @sqlr varchar(2000)

set @sql='select * from Tenancy_Room order by '+case @order when 1 then 'id' when 2 then 'price' else '' end+case @asc when 1 then ' desc' else '' end

exec(@sql)

[Dennis618]

PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
begin
if (@order = 1)
begin
select * from Tenancy_Room
order by id desc --按id排序
end
else if(@order = 2)
begin
select * from Tenancy_Room
order by price --按价格排序
end
else
begin
select * from Tenancy_Room
end

end
--desc在order by 後面加.不加desc的情況默認為asc

[realgz]

PROCEDURE sp_GetSet
(
@order smallint,
@asc smallint
)
AS
select * from Tenancy_Room
order by case
when @order=1 and @asc=1 then id
when @order=1 and @asc=2 then 0-id
when @order=2 and @asc=1 then price
when @order=2 and @asc=2 then 0-price
else newid() --如果选项无效，这句话纯属好玩，叫随机排序
end


大力的启发啊。。。。

[zjcxc]

--用生成SQL语句的方法来实现吧:

create PROCEDURE sp_GetSet
@order smallint, --字段是表中的顺序号
@asc smallint --1为升序,其他值为降序,建议改为bit类型
AS
declare @sql varchar(8000)
select @sql='select * from Tenancy_Room order by '+name
+case @asc when 1 then ' asc' else ' desc' end
from syscolumns where object_id('Tenancy_Room')=id and colid=@order
exec(@sql)
go