Value IN a Variable

Question

Post reply

Value IN a Variable

Richard D. Cushing

Ten Centuries

Points: 1356
More actions
January 5, 2004 at 12:04 pm

#106970

Could someone please tell me WHY the following T-SQL code does NOT work?
======================================
DECLARE @Test varchar(50)
SET @test-2 = '('+'''Rialto'''+','+'''Vernon'''+')'
SELECT @test-2
IF 'Rialto' IN (SELECT @test-2)
SELECT 'True'
ELSE SELECT 'False'
======================================
The value of @test-2 = ('Rialto','Vernon')
If I type:
IF 'Rialto' IN ('Rialto','Vernon') the result is TRUE, but when I substitute the variable @test-2, the result it FALSE.
I have tried constructing the string using QUOTENAME() and serveral other ways, all to no avail. What's happening that makes the CONDITION result in FALSE when I use the variable? Is there a way to fix it so I can use the variable?
Thanks for your help.

Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 1

DECLARE @test-2 varchar(50)

SET @test-2 = '('+'''Rialto'''+','+'''Vernon'''+')'

SELECT @test-2

EXEC ('IF '+'''Rialto'+'''IN '+@Test+' SELECT '+'''True'''+' ELSE SELECT '+'''False''')

Richard D. Cushing Ten Centuries Points: 1356 More actions · Answer 2

Thanks for the quick response. I'll give it a try.

Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

Richard D. Cushing Ten Centuries Points: 1356 More actions · Answer 3

Okay. That works. Now, to carry the question one step further for my project:

I don't want to do a bunch of CASE statements and I want to evaulate an UPDATE along these lines:

=================================

UPDATE <table>

SET <column> = <value>

WHERE <condition>

AND <column value> IN (SELECT @test-2)

=================================

How would the EXEC part fit into this scenario, since it only returns TRUE or FALSE?

NOTE: By the way, I have already solved this particular issue in my present project using a temp table to hold the values (rather than a variable), but I am curious if it can be done using a variable as I had originally intended.

Thanks again for your help.

Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

http://www.algonet.se/~sommar/dynamic_sql.html

Sorry, for once again posting this link. I'll compile my own article on this, so I don't need to refer to that site.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Just for fun. You can also dynamically execute dynamic SQL like this

DECLARE @stmt NCHAR(1000)

SET @stmt = 'USE Frank_3 ' + CHAR(13) + 'DECLARE @stmt NCHAR(1000)' + CHAR(13) +

'SET @stmt = ''CREATE TABLE First (col_a int)''' + CHAR (13) +

'EXEC (@stmt)'

EXEC (@stmt)

PRINT @stmt

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Richard D. Cushing Ten Centuries Points: 1356 More actions · Answer 6

Thanks. Looks like a valuable article. I'm sure I'll gain a lot from it.

Richard D. Cushing
Sr. Consultant
SYNGERGISTIC SOFTWARE SOLUTIONS, LLC