无法在所选查询上传递和追加@变量

ALTER PROCEDURE [dbo].[usp_RmsExecuteValidationRule]        
    @nRuleId INT     
AS 
BEGIN        
    -- Local variables        
    DECLARE @sqlstat AS NVARCHAR(MAX)        
    DECLARE @params  AS NVARCHAR(MAX)        
    DECLARE @RULE_QUERY NVARCHAR(MAX)     
    DECLARE @FIPS  varchar(5) 
    SET @sqlstat = N'SELECT @RULE_QUERY=RULE_QUERY from GdmValidationRuleMaster where RULE_ID = @nRuleId'        
    --SET @sqlstat =  N'SELECT ' + @RULE_QUERY + '=RULE_QUERY from GdmValidationRuleMaster where RULE_ID = ' + @nRuleId 
    SET @params = N'@nRuleId INT,   @RULE_QUERY NVARCHAR(MAX) OUTPUT'        
    EXEC sp_executesql @sqlstat, @params, @nRuleId = @nRuleId, @RULE_QUERY= @RULE_QUERY OUTPUT 
    -- Output   
    SELECT @RULE_QUERY            
END

当我们输入Exec usp_RmsExecuteValidationRule 1时，它返回@RULE_QUERY，即

SELECT GDMID AS GDM_ID from @FIPSName+CADMIN1 WHERE INTPRIORITY IS NULL

这里我故意添加了@FIPSName+ As前缀，因为我有另一个表LCountry与FIPS列:

FIPS
----
GM
FR
UK
AN
BE
BU
BL
BR
BH
CA
AU
VT
CI
CH
CO
CS
EZ
AS
DA
EC
SP
AR
BD
BB
BF
AV
AA
AC
DO
CU
CJ
DR
GP
GJ
HA
JM
ST
RQ
SC
MB
MH
XN
XM
XJ
VC
VI
TK
TD
TB
RN
XL
XK
VQ
GR
GT
GQ
HK
HO
HU
ID
EI
IS
GZ
XC
WE
IN
IT
SM
JA
LS
LU
MX
NU
NL
NO
NZ
PM
PE
RP
PL
PO
RO
SW
SI
LO
ES
TU
TW
SZ
VE
SN
SB
PF
PG
PC
NE
NF
MQ
US
SX
TE
TT
WQ
BQ
DQ
BS
CK
CQ
CR
AY
AT
BV
LQ
KQ
JQ
JU
KT
IO
IP

使用循环，我可以使用Lcountry的ObjectID获取数据。

DECLARE @LoopCounter INT , @MaxBcountryObjId INT, 
        @FIPSName NVARCHAR(100)
SELECT @LoopCounter = min(OBJECTID) , @MaxBcountryObjId = max(OBJECTID) 
FROM dbo.LCOUNTRY
WHILE(@LoopCounter IS NOT NULL
      AND @LoopCounter <= @MaxBcountryObjId)
BEGIN
   SELECT @FIPSName = FIPS
   FROM dbo.LCOUNTRY WHERE OBJECTID = @LoopCounter
   PRINT @FIPSName  
   SET @LoopCounter  = @LoopCounter  + 1 

此查询返回@FIPSName。

我想这个返回值@FIPName应该追加在Rule_Query返回值。同样的值也可以执行

例如:

SELECT GDMID AS GDM_ID from @FIPSName+CADMIN1 WHERE INTPRIORITY IS NULL
Changes Into
SELECT GDMID AS GDM_ID from GM+CADMIN1 WHERE INTPRIORITY IS NULL
Changes Into 
GDMID
------         (The above query must be executed and Return GDMID.)
1198

SELECT @RULE_QUERY=RULE_QUERY from GdmValidationRuleMaster where RULE_ID = @nRuleId