无法在参数中获取变量值

Question

我在select语句参数中未获得变量rsp2的值。

 DECLARE @RSP  varchar(50)
  DECLARE @RSP2 varchar(50)
 SET @RSP =  '(<DL>,<DM>)'
SELECT @RSP  
 SET @RSP2 = REPLACE(REPLACE(REPLACE(REPLACE(@RSP,'(',''),')',''),'<',''''),'>','''')
SELECT @RSP2 
    SELECT [F_YEAR] FROM tbl1 WHERE 
                 RSP  IN (@RSP2)

Answer 1

我假设您想要将f_year的值赋给@rsp变量。

DECLARE @RSP  varchar(50)
      , @RSP2 varchar(50);

 SET @RSP = '(<DL>,<DM>)';
 SET @RSP2 = REPLACE(REPLACE(REPLACE(REPLACE(@RSP,'(',''),')',''),'<',''''),'>','''');

SET @RSP = (
  SELECT Max(f_year)
  FROM   tbl1
  WHERE  RSP  = @RSP2;
);

SELECT @RSP  As rsp
     , @RSP2 As rsp2;

Answer 2

尝尝这个。

DECLARE @RSP  varchar(50)
DECLARE @RSP2 varchar(50)
SET @RSP =  '(<DL>,<DM>)'
SELECT @RSP  
SET @RSP2 = REPLACE(REPLACE(REPLACE(REPLACE(@RSP,'(',''),')',''),'<',''''),'>','''')
SELECT @RSP2 
EXEC('SELECT [F_YEAR] FROM tbl1 WHERE 
             RSP  IN ('+@RSP2+')')

希望这能对你有所帮助

谢谢

Answer 3

DECLARE @RSP  varchar(50) = '(<DL>,<DM>)'
DECLARE @RSP2 varchar(50)
-- next line has been changed as well
SET @RSP2 = REPLACE(REPLACE(REPLACE(REPLACE(@RSP,'(',''),')',''),'<',''),'>','')
SELECT @RSP RSP, @RSP2 RSP2 

SELECT [F_YEAR] FROM tbl1 
WHERE RSP in (
SELECT t.c.value('.', 'VARCHAR(20)')
FROM (
SELECT x = CAST('<t>' + 
    REPLACE(@RSP2, ',', '</t><t>') + '</t>' AS XML)
) a
CROSS APPLY x.nodes('/t') t(c))