Passing multiple values in 1 varchar variable in a stored proc

So in my case, how do I differentiate between the string intended for @.Field
1
from the ones intended for @.Field2?
sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
or do I do something like this:
sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
encased in a double qoute to identify it as one string to be passed over.
THANK YOU! :)
"Mark Williams" wrote:
> You'll need double single-quotes (that's really clear!) to get what you wa
nt
> For example
> DECLARE @.foo varchar(50)
> SET @.foo = '''ABC'', ''DEF'', ''GHI''' --those are all single quotes!
> SELECT @.foo
> returns
> 'ABC', 'DEF', 'GHI'
>
> "ckeaton@.inergex.com" wrote:
>I'm not sure why it's not working when I try to pass the value as it stands
in your most recent example.
It's not finding the individual values which leads me to believe that
something about how the construct is being presented to the database is wron
g.
It should end out looking like this...
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN ('ABC', 'DEF', 'GHI');
But it appears not to be doing that.
Thank you for your suggestions.
Frustrating, because this seems like such a simple thing, but it's tripping
me up! :)
"Mark Williams" wrote:
> You would want something like the second example, but with all single quot
es
> again:
> sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
> "ckeaton@.inergex.com" wrote:
>|||> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is
> wrong.
Maybe you could show us your code? (I, for one, only see three messages so
far in this thread. It appears to have been orphaned in some way.)|||It looks like the statement is evaluating the variable as a single string.
For example, try the following in the Northwind sample database
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1, @.var2)', N'@.var1 varchar(50), @.var2 varchar(50)
',
@.var1='Konbu',@.var2='Tofu'
EXEC sp_executesql N'SELECT * FROM Products
WHERE ProductName IN (@.var1)', N'@.var1 varchar(50)',
@.var1='''Konbu'',''Tofu'''
The first one works, but the second doesn't.
If you wanted to pass your list of coupons as just comma-seperated, you
could do something like this
CREATE PROCEDURE sp_Monkey
@.Field1 varchar(10)
@.Field2 varchar(50)
AS
SET NOCOUNT ON
DECLARE @.coupons TABLE (coupon char(3))
DECLARE @.coupon char(3)
WHILE CHARINDEX(',',@.Field2)<>0
BEGIN
INSERT INTO @.coupons (coupon) VALUES (LEFT(@.Field2, CHARINDEX(',',@.Field2)
- 1))
SET @.Field2=SUBSTRING(@.Field2, CHARINDEX(',', @.Field2) + 1,LEN(@.Field2))
END
INSERT INTO @.coupons (coupon) VALUES (@.Field2)
UPDATE monkey
SET coupon = P.coupon
FROM monkey M, promotions P
WHERE M.recordID = P.recordID
AND P.coupon IN (SELECT coupon FROM @.coupons)
--alternatively AND EXISTS (SELECT coupon FROM @.coupons WHERE
coupon=P.coupon)
The procedure would be called like
sp_Monkey '24601','ABC,DEF,GHI'
The procedure splits the comma-seperated Field2 and inserts each value into
the table variable @.coupons, which is reference later.
"ckeaton@.inergex.com" wrote:

> I'm not sure why it's not working when I try to pass the value as it stand
s
> in your most recent example.
> It's not finding the individual values which leads me to believe that
> something about how the construct is being presented to the database is wr
ong.
> It should end out looking like this...
> UPDATE monkey
> SET coupon = P.coupon
> FROM monkey M, promotions P
> WHERE M.recordID = P.recordID
> AND P.coupon IN ('ABC', 'DEF', 'GHI');
> But it appears not to be doing that.
> Thank you for your suggestions.
> Frustrating, because this seems like such a simple thing, but it's trippin
g
> me up! :)
>|||You would want something like the second example, but with all single quotes
again:
sp_Monkey '24601','''ABC'',''DEF'',''GHI'''
"ckeaton@.inergex.com" wrote:
> So in my case, how do I differentiate between the string intended for @.Fie
ld1
> from the ones intended for @.Field2?
> sp_Monkey '24601', ''ABC'', ''DEF'', ''GHI'' -- all single quotes :)
> or do I do something like this:
> sp_Monkey '24601', " ''ABC'', ''DEF'', ''GHI'' " -- same as before, but
> encased in a double qoute to identify it as one string to be passed over.
> THANK YOU! :)
> "Mark Williams" wrote:
>