Passing multiple string values in as one parameter to a stored procedure

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

SQL Server Development (2000)

Passing multiple string values in as one parameter to a stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-12-11 : 08:08:18

Dia writes "SQL server version: 8.00.760
Win2k prof

I have a stored procedure with a parameter @GroupID. @GroupID can be 1 uniqueidentifier value or multiple uniqueidentifier values. The value is concatenated in VB.NET. I am using @GroupID in the where statement like this (OWNERID is a UID):
WHERE OWNERID IN(@GROUPID)

When I pass in the following as @GroupID

'BC30416F-09D0-41F5-8778-758405908BB4, 67E7CFB8-76A4-478B-9B56-E57BBB22297B'

the records returned are only associated with the first UID passed in the string. If I flip flop the values, the same thing occurs for that value.

I tried passing in the following

"'BC30416F-09D0-41F5-8778-758405908BB4', '67E7CFB8-76A4-478B-9B56-E57BBB22297B'"

and I get "error converting string to uniqueidentifier". But when I put in a print statement right before where the parameter is used it looks like:

'BC30416F-09D0-41F5-8778-758405908BB4', '67E7CFB8-76A4-478B-9B56-E57BBB22297B'

How can I pass in multiple UIDs as a string and have it evaluated correctly in an IN() statement? If this is not possible, how else can I do this?

Thanks,
Dia"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-11 : 08:09:36

There are a couple of articles that describes ways to do this:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=csv

And also look at dynamic SQL (the top 2 articles especially):

http://www.sqlteam.com/SearchResults.asp?SearchTerms=dynamic+sql

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-12-11 : 08:35:34

remember -- IN takes a SET as an argument; you are passing in 1 VALUE as an argument. In my opinion, you should probably get a warning that your "set" in this case is only 1 value. BUT -- it is legal, so SQL allows it. Just because the string happens to be formatted with commas or whatnot doesn't mean it is a set of values -- it is still just 1 single value you are passing in.

If SQL Server changed all strings with commas into sets of values automatically, you'd have trouble with things like:

where Name IN ('Smith, Jeff', 'Volk, Rob')

How would SQL intrepet THAT statement? hopefully this helps clear things up a little as to WHY you need to look at the links Rob provided and find an alternate solution.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources