在过程里面怎么动态取数据库的字段?
表T1 里面的字段动态生成!
现在想取里面某几个字段的值!?
比如:
T1 里面的字段 C1,C2,C3,C4,C5,C6,C7
select C3,C4,C5,C6,C7 from T1
或者
T1 里面的字段 C1,C2,C3,C4,C5,C6,C7,C8,C9
select C3,C4,C5,C6,C7,C8,C9 from T1
C1,C2是固定的!
就是要取C1,C2外的所有字段值!
[解决办法]
declare @str varchar(200)
set @str= 'C3,C4,C5,C6,C7 '
exec( 'select '+@str+ ' from T1 ')
[解决办法]
declare @sql varchar(4000)
set @sql= ' '
select @sql=@sql+ ',[ '+name+ '] '
from syscolumns
where id=object_id( 'T1 ') and name not in( 'C1 ', 'C2 ')
order by colid
set @sql= 'select '+stuff(@sql,1,1, ' ')+ ' from T1 '
exec(@sql)
[解决办法]
create table T(c1 int, c2 int)
if exists(select 1 from syscolumns where id=object_id( 'T ') and colid> 2)
begin
declare @str varchar(1000)
set @str= ' '
select @str=@str+ ', '+name from syscolumns where id=object_id( 'T ') and colid> 2 order by colid
exec( 'select '+stuff(@str, 1, 1, ' ')+ ' from T ')
end
[解决办法]
create procedure sp_test(@Tname varchar(40))
begin
declare @sql varchar(4000)
set @sql= ' '
select
@sql=@sql+ ',[ '+name+ '] '
from
syscolumns
where
id=object_id(@Tname) and name not in( 'C1 ', 'C2 ')
order by
colid
set @sql= 'select '+stuff(@sql,1,1, ' ')+ ' from [ '+@Tname+ '] '
exec(@sql)
end
go
exec sp_test 'T1 '
[解决办法]
--錯了, 改改
create table T(c1 int, c2 int)
alter table T
add c3 int
if exists(select 1 from syscolumns where id=object_id( 'T ') and colid> 2)
begin
declare @str varchar(1000)
set @str= ' '
select @str=@str+ ', '+name from syscolumns where id=object_id( 'T ') and colid> 2 order by colid
set @str=stuff(@str, 1, 1, ' ')
exec( 'select '+@str+ ' from T ')
end
[解决办法]
create procedure pro_col (@tbl_name varchar(30))
as
declare @cur_col varchar(30)
declare @sql varchar(1000)
set @sql= ' '
declare cur_col cursor for
select t2.name from sysobjects t1,syscolumns t2 where t1.id=t2.id and t1.xtype= 'u ' and t1.name= ' '+@tbl_name+ ' ' order by t2.name
open cur_col
fetch next from cur_col into @cur_col
while @@fetch_status = 0
begin
if @cur_col <> 'C1 ' and @cur_col <> 'C2 '
begin
set @sql=@sql+ ' '+@cur_col+ ' , '
end
fetch next from cur_col into @cur_col
end
set @sql= 'select '+substring(@sql,1,len(@sql)-1)+ ' from '+@tbl_name+ ' '
print @sql
exec (@sql)
close cur_col
deallocate cur_col
go
---
exec pro_col T1
[解决办法]
以上为存储过程的实现方法，调用时输入表名
