22,210
社区成员
发帖
与我相关
我的任务
分享
--是需要动态查询某列的值吗?
--下面例子为查询某行某列某表的值
--需要改动的是表名,@i,@j
/*运行环境 SQLSERVER2005*/
-- 构造数据
create table Tb
( a int null,
b int null,
c int null,
d int null
)
insert into Tb select 1,5,3,2
insert into Tb select 2,4,12,12
insert into Tb select 1,30,12,34
insert into Tb select 45,23,9,2
insert into Tb select 16,5,89,2
insert into Tb select 21,5,32,2
declare @tbname varchar(100), -- 表名
@row int, -- 行数
@column int, -- 列数
@data varchar(100) -- 返回值
-- 手动为变量赋值
--**********--
select @tbname ='Tb' --'Tb'
select @row = 1
select @column = 1
--**********--
-- 判断行列值是否超出表范围
if ( @row > ( select count(1) from Tb ) )
begin
print '输入行大于表最大行数'
end
--select @tbname = 'tempdb..' + @tbname --加上所在数据库
if ( @column > ( select max(column_id)
from sys.columns --tempdb.sys.columns
where object_id = object_id(@tbname) ) )
begin
print '输入列大于表最大列数'
end
-- 定义动态SQL
declare @sql nvarchar(2000),
@parm nvarchar(500),
@col_name varchar(100)
select @col_name = name
from sys.columns --tempdb.sys.columns
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