Multi-Valued Parameters - 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)

Multi-Valued Parameters

Author

Topic

rwlopez
Yak Posting Veteran

80 Posts

Posted - 2007-07-26 : 11:41:33

I also posted this on the Reporting Services forum, but am not sure if I will get a response so I thought I would ask here also.

I am trying to pass a Multi-Valued Paramter from a report to a stored procedure, but I am not able to get it to work. The syntax for the parameter is Join(Parameters!SalesID.Value, ", "). It is being passed to the where clause WHERE SalesID IN (@SalesID), but it is not return any results. It does however work it does work if I select only one value from the mulit-valued parameter on the report. Can any one tell what the problem is here.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 11:52:37

Dupe http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86937

E 12°55'05.76"
N 56°04'39.42"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-26 : 11:54:20

You can't do WHERE Column IN (@Variable). You can do WHERE column in (Select * from fnGetList(@SalesId))
where fnGetList is your UDF that returns a table. There are some excellent splitter functions here that can help you do this.

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-26 : 11:57:43

Oh, and where exists (select * from etc.) is better than WHERE column IN ('aaa','bbb',etc.)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-26 : 12:04:48

quote:
Originally posted by jimf

You can't do WHERE Column IN (@Variable).

Yes you can.
You have to do the searching in the SP as dynamic SQL too.
See http://www.sommarskog.se/dynamic_sql.html

E 12°55'05.76"
N 56°04'39.42"

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-07-26 : 13:06:21

I'm missing something

From the linked article:
If you follow the various newsgroups on Microsoft SQL Server, you often see people asking why they can't do:

SELECT * FROM @tablename
SELECT @colname FROM tbl
SELECT * FROM tbl WHERE x IN (@list)

and

SELECT * FROM tbl WHERE col IN (@list)
It is fascinating how may people who put '1,2,3,4' in @list, and then are puzzled why the query above does not return any rows. Well, if there is a row where col has the value '1,2,3,4', you will get a match. These two conditions are the same:

col IN (@list)
col = @list
IN does not mean "parse whatever data there is at runtime as a comma-separated list". It's a compile-time shortcut for col = @a OR col = @b OR ...

This is a very common question on the newsgroups, and Use dynamic SQL is a far too common answer. Yes, you can do this with dynamic SQL, but it is an extremely poor solution. You cannot pass the list as a parameter to sp_executesql, so you would have to use EXEC() and be open to SQL injection. On top of that, for long lists, IN has extremely poor performance – in some tests I did, it took SQL Server 15 seconds to build the query plan for a list with 10000 elements.

The correct method is to unpack the list into a table with a user-defined function or a stored procedure. In my article, Arrays and Lists in SQL Server, I describe a whole range of ways to do this. I also present performance data for the various methods. (Dynamic SQL is at the bottom of that list!) This is a long article, but there are jump-start links in the beginning of the article, depending on which version of SQL Server you are using.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources