select [name] from syscolumns where xtype='U' and object_id('表名') --这样?
select [name] from syscolumns where xtype='U' and object_id('表名') 用这种方法可以取得表字段名,但如何取得其值呢?你可以用游标来给变量赋值 用游标不太方便吧
create proc table_proc @type varchar(20) = null as if @type=null begin declare table_cursor scroll cursor for select * from table end else begin declare table_cursor scroll cursor for select @type from table end declare @w1 int open price_cursor fetch first from table_cursor into @w1 close table_cursor deallocate table_cursor
直接用select @aaa = sum(name) from tab_1 where ** = XX 这样不行??
提供个思路 declare @sql varchar(2000) declare @sum int declare @i int set @i = 0 while(@i <=25) BEGIN set @i = @i + 1 set @sql = 'select @sum=@sum + w' + cast(@i as varchar) + ' from table' exec(@sql) --这里有点问题 END
Declare @FieldStr varchar(2000) Declare @Sqlstr varchar(2000) set @Fieldstr='' set @Sqlstr=''--得到字段表达式 select @fieldstr=@fieldstr+'+'+name from syscolumns where id=object_id('orders') and left([name],1)='w' and cast(substring([name],1,2) as int) between 1 and 25 select @fieldstr=right(@fieldstr,len(@fieldstr)-1)--得到执行结果 select @Sqlstr='Select '+@fieldstr+' from tablename' exec (@Sqlstr)
--这样?
select [name] from syscolumns where xtype='U' and object_id('表名')
用这种方法可以取得表字段名,但如何取得其值呢?你可以用游标来给变量赋值
用游标不太方便吧
@type varchar(20) = null
as
if @type=null
begin
declare table_cursor scroll cursor
for
select * from table
end
else
begin
declare table_cursor scroll cursor
for
select @type from table
end
declare @w1 int
open price_cursor
fetch first from table_cursor into @w1
close table_cursor
deallocate table_cursor
exec table_proc 'w3'
或者 exec table_proc
这是得到一个一条记录的字段值,得到其他可以写个while循环,但是有几个字段必须声明几个变量
OK?只是提供方法
这样不行??
declare @sql varchar(2000)
declare @sum int
declare @i int
set @i = 0
while(@i <=25)
BEGIN
set @i = @i + 1
set @sql = 'select @sum=@sum + w' + cast(@i as varchar) + ' from table'
exec(@sql) --这里有点问题
END
Declare @FieldStr varchar(2000)
Declare @Sqlstr varchar(2000)
set @Fieldstr=''
set @Sqlstr=''--得到字段表达式
select @fieldstr=@fieldstr+'+'+name from syscolumns where id=object_id('orders')
and left([name],1)='w' and cast(substring([name],1,2) as int) between 1 and 25
select @fieldstr=right(@fieldstr,len(@fieldstr)-1)--得到执行结果
select @Sqlstr='Select '+@fieldstr+' from tablename'
exec (@Sqlstr)