34,590
社区成员
发帖
与我相关
我的任务
分享
--SQL查询里如何取得这个表里的第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
declare @name varchar(20),@tablename varchar(20)
set @tablename = 'vmenu' --表名
select top 1 @name = [name] from syscolumns where id = object_id(@tablename) and [name] not in (select top 1 [name] from syscolumns where id = object_id(@tablename))
declare @sql nvarchar(200)
set @sql = 'select '+@name +' from '+@tablename
exec (@sql)
select name from syscolumns s where id=object_id('表名') and s.colid=2
select * from tablename
string str = dt[1]
EXEC MASTER.DBO.xp_execresultset 'SELECT ''SELECT ''+ NAME+'' FROM A''
FROM syscolumns s WHERE id=OBJECT_ID(''A'') AND s.colid=2',N'库'
EXEC MASTER.DBO.xp_execresultset 'SELECT ''SELECT ''+ NAME+'' FROM 表名'' FROM syscolumns s WHERE id=OBJECT_ID(''表名'') AND s.colid=2',N'库'