存储过程用动态SQL去实现! 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--> testexec get_All 'id 1,name 0,' --注意后边有 , 号!
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' gocreate 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--> testexec 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 行受影响)
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,1declare @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 */
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--> testexec get_All 'id 1,name 0,' --注意后边有 , 号!
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'
gocreate 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--> testexec 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 行受影响)
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,1declare @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
*/