27,579
社区成员
发帖
与我相关
我的任务
分享
select case when 第一列 is null then '空' else '非空' end,... from 表名
/*运行环境 SQLSERVER2005*/
-- 构造数据
drop table #t1
create table #T1
( a int null,
b int null,
c int null,
d int null,
e int null,
f int null,
g int null,
h int null,
i int null,
j int null
)
insert into #T1 select 1,null,null,null,null,null,null,null,null,null
insert into #T1 select null,2,null,null,null,null,null,null,null,null
insert into #T1 select null,null,3,null,null,null,null,null,null,null
insert into #T1 select null,null,null,4,null,null,null,null,null,null
insert into #T1 select null,null,null,null,5,null,null,null,null,null
insert into #T1 select null,null,null,null,null,6,null,null,null,null
insert into #T1 select null,null,null,null,null,null,7,null,null,null
insert into #T1 select null,null,null,null,null,null,null,8,null,null
insert into #T1 select null,null,null,null,null,null,null,null,9,null
insert into #T1 select null,null,null,null,null,null,null,null,null,10
declare @tbname varchar(100), -- 表名
@row int, -- 行数 这里假定为第一行,可选择
@column int, -- 列数
@data varchar(100) -- 返回值
-- 手动为变量赋值
--**********--
select @tbname = '#T1'
select @row = 1
select @column = 1
--**********--
-- 判断行列值是否超出表范围
if ( @row > ( select count(1) from #T1 ) )
begin
print '输入行大于表最大行数'
end
select @tbname = 'tempdb..' + @tbname --须要改动 ,因在临时表,在tempdb中
if ( @column > ( select max(column_id)
from tempdb.sys.columns --须要改动,因在临时表,在tempdb中
where object_id = object_id(@tbname) ) )
begin
print '输入列大于表最大列数'
end
-- 定义动态SQL
declare @sql nvarchar(2000),
@parm nvarchar(500),
@col_name varchar(100)
while @column<=10
begin
select @col_name = name
from tempdb.sys.columns --须要改动,因在临时表,在tempdb中
where object_id = object_id(@tbname)
and column_id = @column
select @parm = '@val varchar(10) output'
select @sql = ' select @val = ' + @col_name +
' from ( select *,
row_number()over(order by getdate()) as ordr
from ' + @tbname +
' ) a
where ordr = ' + cast(@row as varchar)
exec sp_executesql @sql,
@parm,
@data output
--select @data
print @data
if @data is not null
begin
set @column=@column+1
end
else
break
end