Simple Stored Procedure Question  
Author Message
dduncan





PostPosted: Sat Oct 27 00:40:24 PDT 2007 Top

SQL Server Developer >> Simple Stored Procedure Question

Hello.

I'd like to add an order (that has any number of line items) to a database,
using stored procedures only. Can this be done with a single stored
procedure, or will I need to call one stored procedure to add an empty order,
and then call another stored procedure to add each line item?

Thanks

SQL Server190  
 
 
Tibor





PostPosted: Sat Oct 27 00:40:24 PDT 2007 Top

SQL Server Developer >> Simple Stored Procedure Question The problem here is to pass the "order object" to the procedure (if you want to do it all in one
procedure). One way of passing something "complex" into a procedure is to pass an XML document and
use OPENXML in your proc to first get the order header and then the order detail rows.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi




> Hello.
>
> I'd like to add an order (that has any number of line items) to a database,
> using stored procedures only. Can this be done with a single stored
> procedure, or will I need to call one stored procedure to add an empty order,
> and then call another stored procedure to add each line item?
>
> Thanks
>

 
 
Erland





PostPosted: Sat Oct 27 02:01:00 PDT 2007 Top

SQL Server Developer >> Simple Stored Procedure Question
> The problem here is to pass the "order object" to the procedure (if you
> want to do it all in one procedure). One way of passing something
> "complex" into a procedure is to pass an XML document and use OPENXML in
> your proc to first get the order header and then the order detail rows.

On SQL 2005, it's better to use the xml methods .nodes and .value, I think.



--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
Tibor





PostPosted: Sat Oct 27 02:18:44 PDT 2007 Top

SQL Server Developer >> Simple Stored Procedure Question > On SQL 2005, it's better to use the xml methods .nodes and .value, I think.

Yes, thanks. These can also be useful. I haven't seen any performance comparison between the two, so
if performance is important, I suggest testing both. I can imagine that if we are to re-use the XML
document several times, than perhaps OPENXML has an advantage (thanks to the preparation step). But
that is only speculation...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi





>> The problem here is to pass the "order object" to the procedure (if you
>> want to do it all in one procedure). One way of passing something
>> "complex" into a procedure is to pass an XML document and use OPENXML in
>> your proc to first get the order header and then the order detail rows.
>
> On SQL 2005, it's better to use the xml methods .nodes and .value, I think.
>
>
>
> --

>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 
 
Erland





PostPosted: Sat Oct 27 06:59:14 PDT 2007 Top

SQL Server Developer >> Simple Stored Procedure Question
> Yes, thanks. These can also be useful. I haven't seen any performance
> comparison between the two, so if performance is important, I suggest
> testing both. I can imagine that if we are to re-use the XML document
> several times, than perhaps OPENXML has an advantage (thanks to the
> preparation step). But that is only speculation...

I guess that what OPENXML does is the same as when the text string
is converted to the represenation of the xml data type, which I assume
happen on the client, so there is no gain there.

In the performance appendix to my article on arrays and lists in SQL Server,
there is data for both .nodes and OPENXML. Generally, .nodes is a lot
faster. But there are some situations on SP2 where the query plan is a
complete disaster.

See http://www.sommarskog.se/arrays-in-sql-perftest.html#XML for details.

Of course, that test is for one specific type XML document and a very
simple one.

One distinct disadvantage with OPENXML is that you need to fiddle
with sp_xml_prepare/removedocument.


--


Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
 
 
Tibor





PostPosted: Sat Oct 27 10:35:25 PDT 2007 Top

SQL Server Developer >> Simple Stored Procedure Question > In the performance appendix to my article on arrays and lists in SQL Server,
> there is data for both .nodes and OPENXML.
<snip>

Interesting...

Thanks, Erland.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi





>> Yes, thanks. These can also be useful. I haven't seen any performance
>> comparison between the two, so if performance is important, I suggest
>> testing both. I can imagine that if we are to re-use the XML document
>> several times, than perhaps OPENXML has an advantage (thanks to the
>> preparation step). But that is only speculation...
>
> I guess that what OPENXML does is the same as when the text string
> is converted to the represenation of the xml data type, which I assume
> happen on the client, so there is no gain there.
>
> In the performance appendix to my article on arrays and lists in SQL Server,
> there is data for both .nodes and OPENXML. Generally, .nodes is a lot
> faster. But there are some situations on SP2 where the query plan is a
> complete disaster.
>
> See http://www.sommarskog.se/arrays-in-sql-perftest.html#XML for details.
>
> Of course, that test is for one specific type XML document and a very
> simple one.
>
> One distinct disadvantage with OPENXML is that you need to fiddle
> with sp_xml_prepare/removedocument.
>
>
> --

>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 
 
BarryBurke





PostPosted: Sun Oct 28 11:46:01 PDT 2007 Top

SQL Server Developer >> Simple Stored Procedure Question If he is using sql 2K he can pass more than 8K data with openxml compared to
xml as a varchar parameter



> > In the performance appendix to my article on arrays and lists in SQL Server,
> > there is data for both .nodes and OPENXML.
> <snip>
>
> Interesting...
>
> Thanks, Erland.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>



> >> Yes, thanks. These can also be useful. I haven't seen any performance
> >> comparison between the two, so if performance is important, I suggest
> >> testing both. I can imagine that if we are to re-use the XML document
> >> several times, than perhaps OPENXML has an advantage (thanks to the
> >> preparation step). But that is only speculation...
> >
> > I guess that what OPENXML does is the same as when the text string
> > is converted to the represenation of the xml data type, which I assume
> > happen on the client, so there is no gain there.
> >
> > In the performance appendix to my article on arrays and lists in SQL Server,
> > there is data for both .nodes and OPENXML. Generally, .nodes is a lot
> > faster. But there are some situations on SP2 where the query plan is a
> > complete disaster.
> >
> > See http://www.sommarskog.se/arrays-in-sql-perftest.html#XML for details.
> >
> > Of course, that test is for one specific type XML document and a very
> > simple one.
> >
> > One distinct disadvantage with OPENXML is that you need to fiddle
> > with sp_xml_prepare/removedocument.
> >
> >
> > --

> >
> > Books Online for SQL Server 2005 at
> > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> > Books Online for SQL Server 2000 at
> > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>
 
 
smita





PostPosted: Sun Oct 28 18:54:35 PDT 2007 Top

SQL Server Developer >> Simple Stored Procedure Question I believe this can be done, probably use cursors pass the OrderID value to the second part of the SP(Insert)

EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com