Passing multiple values in 1 varchar variable in a stored proc IN

I'm trying to pass a parameter to a stored procedure with the following quer
y:
sp_Monkey '24601', " 'ABC', 'DEF', 'GHI' "
The stored procedure is as follows:
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (@.Field2)
Even though @.Field2 should be passed in as multiple string values seperated
by a comma (thus creating a valid "IN" statement) it isn't functioning that
way. If I set the value of @.Field2 = 'ABC' (a single value) the statement
works as desired.ckeaton@.inergex.com (ckeaton@.inergex.com@.discussions.microsoft.com) writes:
> I'm trying to pass a parameter to a stored procedure with the following
> query:
> sp_Monkey '24601', " 'ABC', 'DEF', 'GHI' "
Before we go the actual question, permit me to point out two things. Or
three.
1) Don't name your stored procedures sp_something, the sp_ prefix is
reserved for system stored procedures, and SQL Server first looks
in master for procedures with names like this.
2) In SQL Server you use ' to quote strings. If the setting
QUOTED_IDENTIFER is OFF, you can use " as string delimiter as well.
This is very handy with nested strings, but alas, there is
functionality in SQL Server only works if QUOTED_IDENTIFIER is ON,
so best practice is to use ' only. Note that this setting is ON
by default in many contexts.)
3) And since you appear to get away with this, I suspect that you use
Enterprise Manager to edit your stored procedures. (EM has this
setting off by default for some inexplicable reason.) EM is a very
poor tool to edit stored procedures. You are better off using
Query Analyzer.

> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN (@.Field2)
> Even though @.Field2 should be passed in as multiple string values
> seperated by a comma (thus creating a valid "IN" statement) it isn't
> functioning that way. If I set the value of @.Field2 = 'ABC' (a single
> value) the statement works as desired.
I am afraid that the answers from Mark Williams led you astray. And
the posting from Celko equally less helpful, as he uses syntax that
does not work on SQL Server. (But he claims it to be portable!)
Anyway, the way to do this, is to use a function that unpacks the
list into a table. I have a loooong article on this on my web site,
but this link brings you directly to a solution:
http://www.sommarskog.se/arrays-in-...ist-of-strings.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx