Help with "IN" in stored procedure.

[erich93063]

I have a stored procedure which is performing a search against a "task"
table. I would like to pass in a variable called @strAssignedTo which
will be a comma delimeted list of employee ID's ie: "5,6,10". So my SQL
without this variable would be something like:

WHERE intAssignedTo IN (5,6,10)

but when I try to do:

WHERE intAssignedTo IN (@strAssignedTo)

I get an error saying "cannot convert strAssignedTo to an integer"

What's the proper way to do this? THANKS

[Erland Sommarskog]

[posted and mailed, please reply in news]

(er********@gmail.com) writes:

I have a stored procedure which is performing a search against a "task"
table. I would like to pass in a variable called @strAssignedTo which
will be a comma delimeted list of employee ID's ie: "5,6,10". So my SQL
without this variable would be something like:

WHERE intAssignedTo IN (5,6,10)

but when I try to do:

WHERE intAssignedTo IN (@strAssignedTo)

I get an error saying "cannot convert strAssignedTo to an integer"

Which is because there is no macro expansion going on here. Keep in
mind that you can say things like

WHERE x IN (@a, @b, @c)

Hm, there is actually a macro expansion going on here, since the above
is internally rewritten to

WHERE x = @a OR x = @b OR x = @c

Anyway, for how to this, have a look at
http://www.sommarskog.se/arrays-in-s...st-of-integers.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erich93063]

Thanks for your reply but I couldnt make heads or tails of that
article. It looked like I needed to make a user defined function maybe?
(iter_intlist_to_table )? which I did but then when I tried to do:

SELECT ...
FROM tblTask
WHERE ...
AND intAssignedTo IN
(iter_intlist_to_table(@strAssignedTo))

I got an error saying that the function "iter_intlist_to_table" did not
exist. I'm fairly new to stored procedures and SQL in general so that
article was a bit over my head.

[Erland Sommarskog]

Erich93063 (er********@gmail.com) writes:

Thanks for your reply but I couldnt make heads or tails of that
article. It looked like I needed to make a user defined function maybe?
Yes, that is what the gist of. (There are other methods, but using a
UDF is the best.)
(iter_intlist_to_table )? which I did but then when I tried to do:

SELECT ...
FROM tblTask
WHERE ...
AND intAssignedTo IN
(iter_intlist_to_table(@strAssignedTo))

I got an error saying that the function "iter_intlist_to_table" did not
exist. I'm fairly new to stored procedures and SQL in general so that
article was a bit over my head.

Well, it is easier, if you mimick the example in the article, rather
than trying your own syntax. That's a table-valued function, and
you use a table-valued function just like you use a table. The example
uses JOIN, although in retrospect, I should probably have used EXISTS
instead:

CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
WHERE EXISTS (SELECT *
FROM iter_intlist_to_table(@ids) i
WHERE P.ProductID = i.number)
go

But that's a matter of style only.

Some parts of the article, for instance all the performance tests, are
certainly above novice level. But that is also why I gave a direct
link to a function and example on how to use it.

I suggest that you copy the function, and play with the example in
the article. No way is better to learn, than getting your hands on it
yourself. May take little longer for the actual task at hand, but next
time you need it, you know it better.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[--CELKO--]

This is a common Newbie question. This shows that you don't know what
a scalar parameter is. This is MUCH worse than not knowing SQL. This
is a fundamental programming concept that you should learn in the first
week of any language class.

1) The dangerous, slow kludge is to use dynamic SQL and admit that any
random furure user is a better programmer than you are. It is used by
Newbies who do not understand SQL or even what a compiled language is.
A string is a string; it is a scalar value like any other parameter;
it is not code. This is not just an SQL problem; this is a basic
misunderstanding of programming of principles.

2) Passing a list of parmeters to a stored procedure can be done by
putting them into a string with a separator. I like to use the
traditional comma. Let's assume that you have a whole table full of
such parameter lists:

CREATE TABLE InputStrings
(keycol CHAR(10) NOT NULL PRIMARY KEY,
input_string VARCHAR(255) NOT NULL);

INSERT INTO InputStrings VALUES ('first', '12,34,567,896');
INSERT INTO InputStrings VALUES ('second', '312,534,997,896');
etc.

This will be the table that gets the outputs, in the form of the
original key column and one parameter per row.

CREATE TABLE Parmlist
(keycol CHAR(10) NOT NULL,
parm INTEGER NOT NULL);

It makes life easier if the lists in the input strings start and end
with a comma. You will need a table of sequential numbers -- a
standard SQL programming trick, Now, the query, in SQL-92 syntax
(translate into your local dialect):

INSERT INTO ParmList (keycol, parm)
SELECT keycol,
CAST (SUBSTRING (I1.input_string
FROM S1.seq
FOR MIN(S2.seq) - S1.seq -1)
AS INTEGER)
FROM InputStrings AS I1, Sequence AS S1, Sequence AS S2
WHERE SUBSTRING (',' || I1.input_string || ',' FROM S1.seq FOR 1) =
','
AND SUBSTRING (',' || I1.input_string || ',' FROM S2.seq FOR 1) =
','
AND S1.seq < S2.seq
GROUP BY I1.keycol, I1.input_string, S1.seq;

The S1 and S2 copies of Sequence are used to locate bracketing pairs of
commas, and the entire set of substrings located between them is
extracted and cast as integers in one non-procedural step. The trick
is to be sure that the right hand comma of the bracketing pair is the
closest one to the first comma.

You can then write:

SELECT *
FROM Foobar
WHERE x IN (SELECT parm FROM Parmlist WHERE key_col = :something);

Hey, I can write kludges with the best of them, but I don't. You need
to at the very least write a routine to clean out blanks and
non-numerics in the strings, take care of floating point and decimal
notation, etc. Basically, you must write part of a compiler in SQL.
Yeeeech! Or decide that you do not want to have data integrity, which
is what most Newbies do in practice.

3) The right way is to use tables with the IN () predicate, You set up
the procedure declaration with a "fake array", like this in SQL/PSM
(translate into your local dialect):

CREATE PROCEDURE Foobar ( <other parameters>, IN p1 INTEGER, IN p2
INTEGER, .. IN pN INTEGER) -- default missing values to NULLs
BEGIN
SELECT foo, bar, blah, yadda, ...
FROM Floob
WHERE my_col
IN (SELECT DISTINCT parm
FROM ( VALUES (p1), (p2), .., (pN))
AS ParmList(parm)
WHERE parm IS NOT NULL
AND <other conditions>)
AND <more predicates>;
<more code>;
END;

3) The right way! You load the Parmlist table with values so that each
value is validated by the SQL engine, subject to more constraints and
you have no SQL injection problems. A good optimizer will not need the
SELECT DISTINCT, just a SELECT.