CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Jatinder Singh
Jatinder Singh

Reputation: 11

cant Getting Variable value in parameter

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)

Upvotes: 1

Views: 69

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

Upvotes: 0

Manoj
Manoj

Reputation: 883

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

Upvotes: 0

gvee
gvee

Reputation: 17161

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;

Upvotes: 1

Related Questions