动态SQL语句中输出参数的问题？？达人进

[windspeed]

DECLARE @S VARCHAR(50)
DECLARE @ind INT
DECLARE @cus INT
Declare @strTmp nVarchar(2000)

SELECT @S=CASE FSRC WHEN 6008 THEN 'SALE_PLAN_HTY' WHEN 6009 THEN 'LET_PLAN_HTY' END FROM CUS_REC_EDIT WHERE FID=3

set @strTmp=( 'SELECT @ind= B.FINDUSTRY,@cus=B.FCUSTOMER FROM CUS_REC_EDIT A INNER JOIN
(SELECT * FROM '+ @S + ' ) B ON A.FSRC_ID =B.FID WHERE A.FID=3' )

exec sp_executesql @strTmp,N'@ind int output',@IND OUTPUT,N'@cus int output',@CUS OUTPUT

SELECT @IND,@CUS
-------------
显示错误

消息 137，级别 15，状态 1，第 1 行
必须声明标量变量 "@cus"。
消息 102，级别 15，状态 1，第 2 行
'B' 附近有语法错误。


我想把那个字段的值输出来。。。。就这个要求。。谢谢

[CathySun118]

set @strTmp=( 'SELECT @ind= B.FINDUSTRY,@cus=B.FCUSTOMER FROM CUS_REC_EDIT A INNER JOIN
(SELECT * FROM '+ @S + ' ) B ON A.FSRC_ID =B.FID WHERE A.FID=3' )
这句话有问题，
子查询中select了两个值，不能付值给@strTmp

[paoluo]

DECLARE @S VARCHAR(50)
DECLARE @ind INT
DECLARE @cus INT
Declare @strTmp nVarchar(2000)
Declare @FSRC Int
SELECT @S=CASE FSRC WHEN 6008 THEN 'SALE_PLAN_HTY' WHEN 6009 THEN 'LET_PLAN_HTY' END FROM CUS_REC_EDIT WHERE FID=3

set @strTmp=( 'SELECT @ind= B.FINDUSTRY,@cus=B.FCUSTOMER FROM CUS_REC_EDIT A INNER JOIN
(SELECT * FROM '+ @S + ' ) B ON A.FSRC_ID =B.FID WHERE A.FID=3' )
Print @strTmp
exec sp_executesql @strTmp,N'@ind int output, @cus int output',@IND OUTPUT,@CUS OUTPUT

SELECT @IND,@CUS

[paoluo]

多了點東西，再修改下。


DECLARE @S VARCHAR(50)
DECLARE @ind INT
DECLARE @cus INT
Declare @strTmp nVarchar(2000)
SELECT @S=CASE FSRC WHEN 6008 THEN 'SALE_PLAN_HTY' WHEN 6009 THEN 'LET_PLAN_HTY' END FROM CUS_REC_EDIT WHERE FID=3

set @strTmp=( 'SELECT @ind= B.FINDUSTRY,@cus=B.FCUSTOMER FROM CUS_REC_EDIT A INNER JOIN
(SELECT * FROM '+ @S + ' ) B ON A.FSRC_ID =B.FID WHERE A.FID=3' )
exec sp_executesql @strTmp,N'@ind int output, @cus int output',@IND OUTPUT,@CUS OUTPUT

SELECT @IND,@CUS

[windspeed]

哦，原来格式搞错了。。多谢！！

sp_executesql (Transact-SQL) 2005

MSDN:http://msdn2.microsoft.com/en-us/library/ms188001.aspx