cant Getting Variable value in parameter

Question

i am not getting value of variable rsp2 in select statement parameter.

 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)

What are you trying to achieve? Sorry, it's not clear to me from your question — gvee
– gvee, Commented Sep 9, 2013 at 11:41
why do you feel so ? i executed the same query till "Select @RSP2" I got the result 'DL','DM' — Ashutosh Arya
– Ashutosh Arya, Commented Sep 9, 2013 at 11:42
I think he is saying that when he executes the final SQL statement no results are returned. — Squirrel5853
– Squirrel5853, Commented Sep 9, 2013 at 11:53
It's a common error in SQL, but I've never understood why. Do you not see that a single string that happens to contain commas is, logically, different from several strings separated by commas? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Sep 9, 2013 at 12:52

gvee · Accepted Answer · 2013-09-09 11:42:55Z

1

I assume that you want to get the value of f_year to be assigned to the @rsp variable.

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;

answered Sep 9, 2013 at 11:42

gvee

17.2k42 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Manoj · Accepted Answer · 2013-09-09 12:02:15Z

0

try this.

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+')')

Hope this will help you

Thanks

answered Sep 9, 2013 at 12:02

Manoj

8831 gold badge8 silver badges12 bronze badges

Comments

t-clausen.dk · Accepted Answer · 2013-09-09 12:05:12Z

0

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))

answered Sep 9, 2013 at 12:05

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Collectives™ on Stack Overflow

cant Getting Variable value in parameter

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related