Passing multiple values in 1 varchar variable in a stored proc

Although my earlier posts weren't "perfect," I tested my later posting prett
y
thoroughly in a sample db, and it produces the desired results. In addition,
the code shown is almost exactly the same code as shown at
http://www.aspfaq.com/show.asp?id=2248
which was reference by Aaron Bertrand in a thread very similar to this one.
http://www.microsoft.com/technet/co...94-b4fd1783d45e
So please explain to me how I am leading him "astray."
"Erland Sommarskog" wrote:

> ckeaton@.inergex.com (ckeaton@.inergex.com@.discussions.microsoft.com) writes
:
> 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.
>
> 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
>Thank you Mark Williams for you thorough answers. Your final solution worke
d
perfectly! If you're ever in Rochester, look me up. I owe you a drink.
For the rest of you who were helpful, thank you as well. I will take all of
your advice to heart. I am relatively new to stored procedures on MS SQL
Server, and thank you for your advice.
For our "newbie"-slinging friend, --CELKO--; I hope you can more helpful in
the future. You seem to know what you're talking about, but it gets lost in
the white noise of your apparent hostility.
To all, Happy New Year.
"Mark Williams" wrote:
> Although my earlier posts weren't "perfect," I tested my later posting pre
tty
> thoroughly in a sample db, and it produces the desired results. In additio
n,
> the code shown is almost exactly the same code as shown at
> http://www.aspfaq.com/show.asp?id=2248
> which was reference by Aaron Bertrand in a thread very similar to this one
.
> http://www.microsoft.com/technet/co...94-b4fd1783d45e
> So please explain to me how I am leading him "astray."
> "Erland Sommarskog" wrote:
>sql

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