Showing posts with label intended. Show all posts
Showing posts with label intended. Show all posts

Wednesday, March 28, 2012

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:
>

Friday, March 9, 2012

Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...
A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.Hi Dave,
Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the
Newsgroups: microsoft.public.sqlserver.programming
One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Dave Slinn" <CougarDave@.noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocument and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
||||In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...
Best regards
Michael
"Dave Slinn" <CougarDave@.noemail.noemail> wrote in message
news:%23Rkm201%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocument and the OPENXML function to
> create
> a table from it - no problem with that.
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>

Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post - Sorry)

Note: Sorry for cross posting this question with
microsoft.public.sqlserver.programming, but I actually only intended to post
this question here...
A: I can create an XML fragment using the For XML Auto clause. No problem
so far.
B: I have a stored procedure that takes an XML document as ntext and uses
the extended sproc sp_xml_preparedocument and the OPENXML function to create
a table from it - no problem with that.
Now I want to execute my stored procedure using the XML result of part A as
the parameter for the sproc in part B. Is that possible? The only way I
know how to do it right now is to return the result of A using ADO.NET,
insert a root element (to make the XML valid), and then call the sproc
created in part B with this xml as the parameter.
Hi Dave,
Welcome to MSDN newsgroup.
Regarding on this issue, I've also noticed your another duplicated thread
in the
Newsgroups: microsoft.public.sqlserver.programming
One community member Kent have posted some good suggestion there. Please
have a look and feel free to post in that thread...
Thanks,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
| From: "Dave Slinn" <CougarDave@.noemail.noemail>
| Subject: Pass XML between SPROCs possible in SQL 2000? 2005? (Cross Post
- Sorry)
| Date: Wed, 7 Dec 2005 12:36:59 -0600
| Lines: 18
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.3790.1830
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#Rkm201#FHA.3804@.TK2MSFTNGP14.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.xml
| NNTP-Posting-Host: online.gms.ca 142.165.52.112
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP14.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.xml:26733
| X-Tomcat-NG: microsoft.public.sqlserver.xml
|
| Note: Sorry for cross posting this question with
| microsoft.public.sqlserver.programming, but I actually only intended to
post
| this question here...
|
| A: I can create an XML fragment using the For XML Auto clause. No problem
| so far.
|
| B: I have a stored procedure that takes an XML document as ntext and uses
| the extended sproc sp_xml_preparedocument and the OPENXML function to
create
| a table from it - no problem with that.
|
| Now I want to execute my stored procedure using the XML result of part A
as
| the parameter for the sproc in part B. Is that possible? The only way I
| know how to do it right now is to return the result of A using ADO.NET,
| insert a root element (to make the XML valid), and then call the sproc
| created in part B with this xml as the parameter.
|
|
|
|||In SQL Server 2005, you can utilize the XML datatype to do this very easily.
In SQL Server 2000, FOR XML goes always back to the client only, so using
ADO/OLEDB/ADO.Net in between is necessary...
Best regards
Michael
"Dave Slinn" <CougarDave@.noemail.noemail> wrote in message
news:%23Rkm201%23FHA.3804@.TK2MSFTNGP14.phx.gbl...
> Note: Sorry for cross posting this question with
> microsoft.public.sqlserver.programming, but I actually only intended to
> post this question here...
> A: I can create an XML fragment using the For XML Auto clause. No problem
> so far.
> B: I have a stored procedure that takes an XML document as ntext and uses
> the extended sproc sp_xml_preparedocument and the OPENXML function to
> create
> a table from it - no problem with that.
> Now I want to execute my stored procedure using the XML result of part A
> as
> the parameter for the sproc in part B. Is that possible? The only way I
> know how to do it right now is to return the result of A using ADO.NET,
> insert a root element (to make the XML valid), and then call the sproc
> created in part B with this xml as the parameter.
>