IN clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

IN clause

Author

Topic

dcarva
Posting Yak Master

140 Posts

Posted - 2005-02-23 : 23:38:50

Why doesn't this work?

DECLARE @fields varchar(150)
SET @fields = char(39) + 'useridmin' + char(39) + ',' + char(39) + 'useridmax' + char(39)
PRINT @fields

SELECT [Value]
FROM Cart_Settings
WHERE Name IN (@fields)

Shouldn't it be the equivalent of:
select [value] from cart_settings where name in ('useridmin','useridmax')

The PRINT staments prints out the string correctly. If I manually enter the @fields value, then it returns data. it just doesn't work with a variable. I need to pass this string into a stored proc and use it in the IN clause. Thanks!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-02-24 : 00:01:23

DECLARE @fields varchar(150)
SET @fields = 'SELECT [Value] FROM Cart_Settings WHERE Name IN (' + char(39) + 'useridmin' + char(39) + ',' + char(39) + 'useridmax' + char(39) + ')'
print (@fields)
EXEC(@fields)

--But a possibly better solution would be

SELECT
a.Value
FROM
Cart_Settings AS a
JOIN
(SELECT 'useridmin' as Name
UNION
SELECT 'useridmax') AS b
ON a.Name = b.Name

Duane.

dcarva
Posting Yak Master

140 Posts

Posted - 2005-02-24 : 00:05:42

Thanks Duane. here is something I just tried:

DECLARE @fields char(150)
declare @sql varchar(150)
SET @fields = 'useridmin,useridmax'

SET @fields = CHAR(39) + REPLACE( @fields, ',', CHAR(39) + ',' + CHAR(39)) + CHAR(39) + ')'
SET @sql = 'SELECT [Value] FROM Cart_Settings WHERE [Name] IN (' + @fields
print @sql
exec @sql

@fields represents exactly how it's passed in. The results of this query returns:

SELECT [Value] FROM Cart_Settings WHERE [Name] IN ('useridmin','useridmax')
Server: Msg 203, Level 16, State 2, Line 12
The name 'SELECT [Value] FROM Cart_Settings WHERE [Name] IN ('useridmin','useridmax')

It looks like I formatted it right...thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-24 : 07:27:54

exec(@sql)

EDIT: oh... and read up in BOL on sp_executesql. If you are going to use dynamic sql, using sp_executesql will allow exec plans to be cached.

Corey

"If the only tool you have is a hammer, the whole world looks like a nail." - Mark Twain

dcarva
Posting Yak Master

140 Posts

Posted - 2005-02-27 : 17:45:06

That's it. It works. Thanks!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources