create table test (col1 int,col2 int) insert into test select 1,1 union all select 2,2 union all select 3,4 --假设查询第二列 declare @s varchar(10) set @s=(select top 1 name from syscolumns where name not in(select top 1 name from syscolumns where id=object_id('test'))and id=object_id('test')) exec('select '+@s+' from test') drop table test
就是说,假如我有一个表student(如下): stu_id stu_name stu_sex stu_age 当我要检索满足条件的学生姓名的时候(select stu_name from student where....),其中的stu_name可以不以用它在表中的列号(2)替代,如果能,又有什么约束?
--测试数据 create table test (col1 int,col2 int) insert into test select 1,1 union all select 2,2 union all select 3,4 go --示例 --返回@tname表的第@colid列的列名 create function dbo.getcname(@tname varchar(10), @colid int) returns varchar(100) as begin declare @cname varchar(100) select @cname=name from syscolumns where id=object_id(@tname) and colid=@colid return @cname end go --注意列名不能用变量, 所以只能用动态sql语句 declare @str varchar(100) set @str='select '+dbo.getcname('test', 1) +','+dbo.getcname('test', 2) +' from test ' --print @str exec (@str) --清除 drop function dbo.getcname drop table test
create table test (col1 int,col2 int)
insert into test
select 1,1 union all
select 2,2 union all
select 3,4
--假设查询第二列
declare @s varchar(10)
set @s=(select top 1 name from syscolumns
where name not in(select top 1 name from syscolumns where id=object_id('test'))and id=object_id('test'))
exec('select '+@s+' from test')
drop table test
-----------
1
2
4不过我的取得第二列名称的语句太过于复杂 应该还有更简单的方法:)
stu_id stu_name stu_sex stu_age
当我要检索满足条件的学生姓名的时候(select stu_name from student where....),其中的stu_name可以不以用它在表中的列号(2)替代,如果能,又有什么约束?
create table test (col1 int,col2 int)
insert into test
select 1,1 union all
select 2,2 union all
select 3,4
go
--示例
--返回@tname表的第@colid列的列名
create function dbo.getcname(@tname varchar(10), @colid int)
returns varchar(100)
as
begin
declare @cname varchar(100)
select @cname=name
from syscolumns
where id=object_id(@tname)
and colid=@colid
return @cname
end
go
--注意列名不能用变量, 所以只能用动态sql语句
declare @str varchar(100)
set @str='select '+dbo.getcname('test', 1)
+','+dbo.getcname('test', 2)
+' from test '
--print @str
exec (@str)
--清除
drop function dbo.getcname
drop table test