create proc get_All(@order varchar(1000))
as
begin
declare @sql varchar(8000)
set @sql = 'select * from tb '
if @order is not null
set @order = replace(replace(@order,' 1,',' asc,'),' 0,',' desc,')
else 
set @order = ''
set @sql = @sql + ' ' + left(@order,len(@order)-1)
exec(@sql)
end
go

--> test

exec get_All 'id 1,name 0,'  --注意后边有 , 号！

------解决方案--------------------
SQL code

create table tb(id int,name varchar(10))
insert into tb
select 1,'a' union all
select 1,'b' union all
select 2,'c' union all
select 2,'a' union all
select 11,'c'
go

create proc get_All(@order varchar(1000))
as
begin
declare @sql varchar(8000)
set @sql = 'select * from tb '
if @order is not null
set @order = ' order by ' + replace(replace(@order,' 1,',' asc,'),' 0,',' desc,')
else 
set @order = ''
set @sql = @sql + ' ' + left(@order,len(@order)-1)
exec(@sql)
end
go

--> test

exec get_All 'id 1,name 0,'  --注意后边有 , 号！

drop proc get_All
drop table tb

/*******************

id          name
----------- ----------
1           b
1           a
2           c
2           a
11          c

(5 行受影响)

------解决方案--------------------

SQL code

create table test1015(专业 int,寝室号 int,班级 int,年级 int)
insert into test1015
select 1,11,1,1 union all
select 1,11,2,1 union all
select 1,12,3,1 union all
select 2,9,1,2 union all
select 2,9,2,2 union all
select 2,8,3,1

declare @param varchar(30)
set @param='年级,班级,寝室号,专业' --不需要1,2,3,4你直接控制它们的位置就可以了。

declare @sql varchar(8000)
set @sql='select * from test1015 order by '+@param
exec(@sql)
drop table test1015

/*
专业          寝室号         班级          年级
----------- ----------- ----------- -----------
1           11          1           1
1           11          2           1
2           8           3           1
1           12          3           1
2           9           1           2
2           9           2           2
*/