Dynamic 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 2005 Forums

Transact-SQL (2005)

Dynamic IN() clause

Author

Topic

jimsurf
Starting Member

18 Posts

Posted - 2009-07-10 : 14:25:17

Anybody know the best SQL Server 2005 way to create a dynamic IN clause
The ManagerID is a unique identifier, and the parameter is a nullable filter

DECLARE @ManagerIDS varchar(Max)

--Must Handle either null, or value based variable
Set @ManagerIDS = null
SET @ManagerIDS = 'EDE6DC1A-05DB-4B67-A314-047C1579C82A' + ',' + '2E737FAF-41D8-426A-869F-09F713C8D9D5'

SELECT * FROM Asset
WHERE ManagerID IN(@ManagerIDS)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-10 : 14:26:42

DECLARE @ManagerIDS varchar(Max)

--Must Handle either null, or value based variable
Set @ManagerIDS = null
SET @ManagerIDS = 'EDE6DC1A-05DB-4B67-A314-047C1579C82A' + ',' + '2E737FAF-41D8-426A-869F-09F713C8D9D5'

exec ('SELECT * FROM Asset
WHERE ManagerID IN(' + @ManagerIDS + ')')

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

jimsurf
Starting Member

18 Posts

Posted - 2009-07-10 : 14:28:37

I am trying to find a non-dynamic sql statement way to do it.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-07-10 : 14:34:57

Your options are as follows

1. Use dynamic Sql. (Illustrated above, but also look into sp_ExecuteSql with BOL)
2. Create a table to hold your managerids and join it in the procedure.
3. PASS EACH ManagerIDS to a seperate parameter, then code accordingly based on the # of parameters in your procedure.

Other than that, there are not many othe options.

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-10 : 15:40:49

Here is another option, albeit bad:

SELECT * FROM Asset
WHERE '%,' + CAST(ManagerID AS VARCHAR(36)) + ',%' LIKE ',' + @ManagerIDS + ','

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2009-07-10 : 18:09:42

http://www.sommarskog.se/arrays-in-sql-2005.html

elsasoft.org

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-11 : 06:14:57

quote:
Originally posted by Lamprey

Here is another option, albeit bad:
SELECT * FROM Asset
WHERE '%,' + CAST(ManagerID AS VARCHAR(36)) + ',%' LIKE ',' + @ManagerIDS + ','

That should be

SELECT * FROM Asset
WHERE ',' + @ManagerIDS + ',' LIKE '%,' + CAST(ManagerID AS VARCHAR(36)) + ',%'

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-07-13 : 12:52:31

quote:
Originally posted by madhivanan

quote:
Originally posted by Lamprey

Here is another option, albeit bad:
SELECT * FROM Asset
WHERE '%,' + CAST(ManagerID AS VARCHAR(36)) + ',%' LIKE ',' + @ManagerIDS + ','

That should be

SELECT * FROM Asset
WHERE ',' + @ManagerIDS + ',' LIKE '%,' + CAST(ManagerID AS VARCHAR(36)) + ',%'

Madhivanan

Failing to plan is Planning to fail

Opps, thanks for the correction. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources