IN operand ( subquery || list ) as sp parameter

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 operand ( subquery || list ) as sp parameter

Author

Topic

robertkerr
Starting Member

1 Post

Posted - 2002-07-30 : 09:49:25

MSSQL 7.0

Using an IN predicate to a WHERE requires the operand be a subquery or list type. One runs into problems when trying to pass the list via a parameter in a stored procedure. Thus,

SELECT c1, c2 FROM t1 WHERE c1 in (10,20,30,35,38,43)

works fine and dandy, even when c1 is a char type and using quoted values such as ('aa','bb','cd','ef').

But one runs into problems when trying the same thing via a stored procedure:

CREATE procedure sp1 (@parm1 varchar(300)) as
select c1, c2 from t1 where c1 in (@parm1)

Opening SQL Analyzer and sending:

sp1 'aa','bb','cd','ef'

Won't work because there are too many parameters.

sp1 CStr('aa','bb','cd','ef')

is accepted, and the string is definitely in @parm1, however it is a string, not a list, and returns no records.

There are obvious ways to solve this with looping, and I know how to do that. But this seems like such a trivial thing to do I cannot believe MSSQL cannot convert a string to a list!

Ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-07-30 : 09:54:07

Dynamic SQL to the rescue!

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

And AFAIK no other database product can do it either, without using some kind of dynamic SQL.

Edited by - robvolk on 07/30/2002 09:54:22

KHeon
Posting Yak Master

135 Posts

Posted - 2002-07-30 : 10:33:53

Just guessing here, and not totally sure how'd it would be accomplished, but couldn't you use a 'table' variable, populated with the 'in-list' values and then use that in the FROM clause?

Like I said, haven't thought this one out much, but if you can figure how to get the 'in-list' into a table variable I'd imagine you can query against that.

Anyone?

EDIT: Aw, forget this, just realized you were talking about SQL7, table variables were introduced with SQL2000. Sorry 'bout that.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Edited by - KHeon on 07/30/2002 10:34:33

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources