如表A中的字段 FA,里面存放的是另一个表B的字段名;如何使用一条SQL语句显示出来如 A 中 FA=S002
而 B中 S002的值是 张山
select A.FA002 from A,B where ...
以上语句 返回的是 S002;想返回 张三;如何做
而 B中 S002的值是 张山
select A.FA002 from A,B where ...
以上语句 返回的是 S002;想返回 张三;如何做
declare @num int, @sqls nvarchar(4000)
set @sqls='select count(*) from tableName'
exec(@sqls) --如何将exec执行结果放入变量中? declare @num int, @sqls nvarchar(4000)
set @sqls='select @a=count(*) from tableName '
exec sp_executesql @sqls,N'@a int output',@num output
select @num
set @name=(select name from syscolumns s join A on s.name = A.FA where id=OBJECT_ID('B') )
exec('select '+@name +' from B')
-- Author :SQL77(只为思齐老)
-- Date :2010-02-01 17:15:07
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#A
if object_id('A') is not null drop table A
go
create table A([FA] varchar(4))
insert A
select 'S002'
--> 测试数据:#B
if object_id('B') is not null drop table B
go
create table B([S002] varchar(4))
insert B
select '张山'
--------------开始查询--------------------------
DECLARE @NAME VARCHAR(20)
SELECT @NAME=ISNULL(@NAME+',','')+'['+NAME+']'
FROM SYSCOLUMNS
WHERE ID=OBJECT_ID('B')
AND NAME IN(SELECT FA FROM A)
EXEC('SELECT '+@NAME+' FROM B')----------------结果----------------------------
/* (所影响的行数为 1 行)
(所影响的行数为 1 行)S002
----
张山@NAME
*/