mardi 27 septembre 2016

Not able to Append @FIPSName value on the selected Query using Procedure

ALTER PROCEDURE [dbo].[usp_RmsExecuteValidationRule]        

-- Parameters           
 @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

When we Exec usp_RmsExecuteValidationRule 1 It Returns @RULE_QUERY i.e:

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

Here I have intentionally added @FIPSName+ As prefix because

I have another table LCountry table which FIPS coulumn

When we It Returns

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

Using Loop I am able fetch data using ObjectID of Lcountry.

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 

This query Return @FIPSName.

QUESTION

I want to This return Value @FIPName Should Appends on Rule_Query return Value. and the same value can be executed too.

For Example :

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

Aucun commentaire:

Enregistrer un commentaire