declare @a varchar(8000)
set @a=''
select @a=@a+','+a.name from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and a.name='表名' and b.name like '%int'set @a=right(@a,len(@a)-1)exec('select '+@a+' from 表名')
set @a=''
select @a=@a+','+a.name from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and a.name='表名' and b.name like '%int'set @a=right(@a,len(@a)-1)exec('select '+@a+' from 表名')
向 substring 函数传递了无效的 length 参数。并且报:服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'from' 附近有语法错误。如果去掉 SET @a = RIGHT(@a, len(@a)-1) 或改为 SET @a = RIGHT(@a, len(@a))进行测试,仍然报错误:服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'from' 附近有语法错误。
是否在这一句:exec('select '+@a+' from 表名') 有什么问题啊?我实在看不出来,就请各位再次指点一下吧!!另外 set @a=right(@a,len(@a)-1)
这一句是什么意思啊?
insert into x values (1,3,5,4,'2003-10-8','4','6','3',7,8)declare @a varchar(8000)
set @a=''
select @a=@a+','+a.name from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and a.name='X' and b.name like '%int'set @a=right(@a,len(@a)-1)exec('select '+@a+' from X')drop table x
-------------------------------------
(所影响的行数为 1 行)服务器: 消息 536,级别 16,状态 1,行 8
向 substring 函数传递了无效的 length 参数。
服务器: 消息 156,级别 15,状态 1,行 1
在关键字 'from' 附近有语法错误。
---------------------------------------------------------------------不行啊!!From附近错在那儿啊?
create table X (a int,b int,c int,d int,e datetime,f char,g varchar,h varchar,i int,j int)
insert into x values (1,3,5,4,'2003-10-8','4','6','3',7,8)declare @a varchar(8000)
set @a=''
select @a=@a+','+a.name from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.name='X' and b.name like '%int'set @a=right(@a,len(@a)-1)exec('select '+@a+' from X')drop table x
meteorlg()的運行正确了.
insert into x values (1,3,5,4,'2003-10-8','4','6','3',7,8)改成
insert into x values (NULL,3,5,4,'2003-10-8','4','6','3',NULL,8)在结果中,能不能不显示为NUll的结果呢?给答复者另开贴给分!!!
declare @b varchar(8000)
set @b=''
select @b=@b+' and '+a.name+' is not null' from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.name='表名' and b.name like '%int'exec('select '+@a+' from X where 1=1'+@b)
----------------
create table X (a int,b int,c int,d int,e datetime,f char,g varchar,h varchar,i int,j int)
insert into x values (NULL,3,5,4,'2003-10-8','4','6','3',NULL,8)declare @a varchar(8000)
set @a=''
select @a=@a+','+a.name from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.name='X' and b.name like '%int'set @a=right(@a,len(@a)-1)declare @b varchar(8000)
set @b=''
select @b=@b+' and '+a.name+' is not null' from syscolumns a, systypes b,sysobjects d where a.xtype=b.xusertype and a.id=d.id and d.name='X' and b.name like '%int'exec('select '+@a+' from X where 1=1'+@b)drop table x
--------------------结果是一条记录都没有显示出来了!我希望的是能列出:b,c,d,j3,5,4,8看来有什么地方有点问题,请高手指正一下吧!!
NULL,3,5,4,'2003-10-8','4','6','3',NULL,8
1,NULL,NULL,4,'2003-10-8','4','6','3',6,8
应该显示
d,j
4,8
?
如果
1,NULL,NULL,4,'2003-10-8','4','6','3',6,8那么应显示:
a,d,i,j
1 4 6 8它过滤掉的其实是为NULL的字段,并不去掉不为Null的字段,如不为Null,那么仍然显示它!!
insert into X values (1,null,5,4,'2003-10-8','4','6','3',7,8)declare @a varchar(8000),@b varchar(100)
set @a=''
set @b=''
create table #t(col varchar(100))
declare c1 cursor fast_forward for select a.name from syscolumns a, systypes b,sysobjects d
where a.xtype=b.xusertype and a.id=d.id and d.name='X' and b.name like '%int'
open c1
fetch next from c1 into @b
while @@fetch_status=0
begin
exec('if not exists (select '+@b+' from X where '+@b+' is null) insert into #t values('''+@b+''')')
fetch next from c1 into @b
end
close c1
deallocate c1
select @a=@a+','+col from #t
drop table #t
set @a=right(@a,len(@a)-1)
select @a
exec('select '+@a+' from X')drop table X
--------------------------------
你太强了,我服了你!!给分!!