Posted by Erland Sommarskog on 08/03/05 02:05

Erich93063 (erich93063@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, esquel@sommarskog.se

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

[Back to original message]