Insert multiple rows with stored procedures

Consider passing the order as XML and use OPENXML (if 2000) or .Nodes (if 2005). You will find more
info in general on this topic at www.sommarskog.se.

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


"Ricardo Luceac" wrote in message
>
> Hi all...
>
> How can I pass multiple rows parameters for a stored procedure??
>
> For example:
>
> I have an order table and a orderitens table.
>
> I need to insert the order, and insert the orderitems with the
> id of teh order... But te orderitems will have more than 1...
>
>
> thx...
>
> *** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: Insert multiple rows with stored procedures 

Hi Ricardo,

Nowadays using XML is the best option (better than comma separated values
unless you have strong network bandwidth restrictions) . In SQL Server 2008
you have table valued parameters that will solve your problem in a efficient
and elegant way

--

Rubén Garrigós
Solid Quality Mentors

"Ricardo Luceac" wrote in message

>
> Hi all...
>
> How can I pass multiple rows parameters for a stored procedure??
>
> For example:
>
> I have an order table and a orderitens table.
>
> I need to insert the order, and insert the orderitems with the
> id of teh order... But te orderitems will have more than 1...
>
>
> thx...
>
> *** Sent via Developersdex http://www.developersdex.com ***
 >> Stay informed about: Insert multiple rows with stored procedures 

Hi Friend,

You can use the follwowing format of SP to insert multiple rows of data in a single SP.

You can pass values as comma seperated values.

For the following SP
Input can be:

MemberId =10

MemberProductId =12,14,35,67,89


CREATE PROCEDURE dbo.insertMemberProductFavorite
(
@MemberId int,
@MemberProductId varchar(100)

)
AS
DECLARE @count int
DECLARE @str VARCHAR(8000)
DECLARE @spot SMALLINT
WHILE @MemberProductId <> ''
BEGIN
SET @spot = CHARINDEX(',', @MemberProductId)
IF @spot>0
BEGIN
SET @str = CAST(LEFT(@MemberProductId, @spot-1) AS INT)
SET @MemberProductId = RIGHT(@MemberProductId, LEN(@MemberProductId)-@spot)
END
ELSE
BEGIN
SET @str = CAST(@MemberProductId AS INT)
SET @MemberProductId = ''
END
SELECT @count=count(1) FROM MemberProductFavorite
WHERE MemberProductId=@str
AND MemberId=@MemberId
IF @count =0
BEGIN
INSERT INTO MemberProductFavorite
(MemberId,
MemberProductId)
VALUES
( @MemberId,
@str)
END
END
RETURN
GO

Enjoy Coding

Thanks & Regards,

Fouzan.Y.



Ricardo Luceac wrote:

Insert multiple rows with stored procedures
31-Jan-08

Hi all...

How can I pass multiple rows parameters for a stored procedure??

For example:

I have an order table and a orderitens table.

I need to insert the order, and insert the orderitems with the
id of teh order... But te orderitems will have more than 1...


thx...

*** Sent via Developersdex http://www.developersdex.com ***

Previous Posts In This Thread:

On Thursday, January 31, 2008 5:33 AM
Ricardo Luceac wrote:

Insert multiple rows with stored procedures
Hi all...

How can I pass multiple rows parameters for a stored procedure??

For example:

I have an order table and a orderitens table.

I need to insert the order, and insert the orderitems with the
id of teh order... But te orderitems will have more than 1...


thx...

*** Sent via Developersdex http://www.developersdex.com ***

On Thursday, January 31, 2008 5:41 AM
Tibor Karaszi wrote:

Consider passing the order as XML and use OPENXML (if 2000) or .
Consider passing the order as XML and use OPENXML (if 2000) or .Nodes (if 2005). You will find more
info in general on this topic at www.sommarskog.se.

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


"Ricardo Luceac" wrote in message

On Thursday, January 31, 2008 5:43 AM
Jack Vamvas wrote:

Re: Insert multiple rows with stored procedures
Two potential options:
1)Pass in as an array , but then you will need to split the array and run
the relevant INSERT multiple times within the stored procedure
2)Run the stored procedure(s) multiple times from within your application

Normally , I prefer option 2) , but this depends on the application

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"Ricardo Luceac" wrote in message


On Thursday, January 31, 2008 6:17 AM
novalidaddres wrote:

Hi Ricardo,Nowadays using XML is the best option (better than comma separated
Hi Ricardo,

Nowadays using XML is the best option (better than comma separated values
unless you have strong network bandwidth restrictions) . In SQL Server 2008
you have table valued parameters that will solve your problem in a efficient
and elegant way :)

--

Rub?n Garrig?s
Solid Quality Mentors

"Ricardo Luceac" wrote in message



Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Process Killer Revisited
http://www.eggheadcafe.com/tutorials/aspnet/761a922d-7b70-4df6-9e23-4a...d7bc912
 >> Stay informed about: Insert multiple rows with stored procedures 

Hi
There are so many techniques on the internet

DECLARE @MemberId INT,@MemberProductId VARCHAR(20)

SET @MemberId =10

SET @MemberProductId ='12,14,35,67,89'

INSERT INTO....

SELECT @MemberId,Item FROM

ListString(@MemberProductId)

/*

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER function [dbo].[ListString] (@List varchar(8000))

returns table

as return (

select substring(List, [Number] + 1,

charindex(',', List, [Number] + 1) - ([Number] +1 )) Item

from (select ',' + @List + ',' List) OL

join numbers on substring(List, [Number], 4000) like ',_%'

and [Number] betwe

*/





<Fouzan Yoosuf> wrote in message ...
> Hi Friend,
>
> You can use the follwowing format of SP to insert multiple rows of data in
> a single SP.
>
> You can pass values as comma seperated values.
>
> For the following SP
> Input can be:
>
> MemberId =10
>
> MemberProductId =12,14,35,67,89
>
>
> CREATE PROCEDURE dbo.insertMemberProductFavorite
> (
> @MemberId int,
> @MemberProductId varchar(100)
>
> )
> AS
> DECLARE @count int
> DECLARE @str VARCHAR(8000)
> DECLARE @spot SMALLINT
> WHILE @MemberProductId <> ''
> BEGIN
> SET @spot = CHARINDEX(',', @MemberProductId)
> IF @spot>0
> BEGIN
> SET @str = CAST(LEFT(@MemberProductId, @spot-1) AS INT)
> SET @MemberProductId = RIGHT(@MemberProductId,
> LEN(@MemberProductId)-@spot)
> END
> ELSE
> BEGIN
> SET @str = CAST(@MemberProductId AS INT)
> SET @MemberProductId = ''
> END
> SELECT @count=count(1) FROM MemberProductFavorite
> WHERE MemberProductId=@str
> AND MemberId=@MemberId
> IF @count =0
> BEGIN
> INSERT INTO MemberProductFavorite
> (MemberId,
> MemberProductId)
> VALUES
> ( @MemberId,
> @str)
> END
> END
> RETURN
> GO
>
> Enjoy Coding
>
> Thanks & Regards,
>
> Fouzan.Y.
>
>
>
> Ricardo Luceac wrote:
>
> Insert multiple rows with stored procedures
> 31-Jan-08
>
> Hi all...
>
> How can I pass multiple rows parameters for a stored procedure??
>
> For example:
>
> I have an order table and a orderitens table.
>
> I need to insert the order, and insert the orderitems with the
> id of teh order... But te orderitems will have more than 1...
>
>
> thx...
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
> Previous Posts In This Thread:
>
> On Thursday, January 31, 2008 5:33 AM
> Ricardo Luceac wrote:
>
> Insert multiple rows with stored procedures
> Hi all...
>
> How can I pass multiple rows parameters for a stored procedure??
>
> For example:
>
> I have an order table and a orderitens table.
>
> I need to insert the order, and insert the orderitems with the
> id of teh order... But te orderitems will have more than 1...
>
>
> thx...
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
> On Thursday, January 31, 2008 5:41 AM
> Tibor Karaszi wrote:
>
> Consider passing the order as XML and use OPENXML (if 2000) or .
> Consider passing the order as XML and use OPENXML (if 2000) or .Nodes (if
> 2005). You will find more
> info in general on this topic at www.sommarskog.se.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Ricardo Luceac" wrote in message
>
>
> On Thursday, January 31, 2008 5:43 AM
> Jack Vamvas wrote:
>
> Re: Insert multiple rows with stored procedures
> Two potential options:
> 1)Pass in as an array , but then you will need to split the array and run
> the relevant INSERT multiple times within the stored procedure
> 2)Run the stored procedure(s) multiple times from within your application
>
> Normally , I prefer option 2) , but this depends on the application
>
> --
>
> Jack Vamvas
> ___________________________________
> Search IT jobs from multiple sources- http://www.ITjobfeed.com
>
>
>
>
> "Ricardo Luceac" wrote in message
>
>
> On Thursday, January 31, 2008 6:17 AM
> novalidaddres wrote:
>
> Hi Ricardo,Nowadays using XML is the best option (better than comma
> separated
> Hi Ricardo,
>
> Nowadays using XML is the best option (better than comma separated values
> unless you have strong network bandwidth restrictions) . In SQL Server
> 2008
> you have table valued parameters that will solve your problem in a
> efficient
> and elegant way
>
> --
>
> Rub?n Garrig?s
> Solid Quality Mentors
>
> "Ricardo Luceac" wrote in message
>
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> ASP.NET Process Killer Revisited
> http://www.eggheadcafe.com/tutorials/aspnet/761a922d-7b70-4df6-9e23-4a...d7bc912
 >> Stay informed about: Insert multiple rows with stored procedures 

In Sql Server 2005, my preferred method is passing in the data via xml, and
shredding the xml.
Most times I shred the xml into @variable or #temp tables. Then I interact
with the @variable or #temp table when I need to insert data into the real
tables.
See:
http://pratchev.blogspot.com/2007/06/shredding-xml-in-sql-server-2005.html


In 2008,
http://www.google.com/#hl=en&safe=active&q=sql+server+2008+table+value...rameter

.........


<Fouzan Yoosuf> wrote in message ...
> Hi Friend,
>
> You can use the follwowing format of SP to insert multiple rows of data in
> a single SP.
>
> You can pass values as comma seperated values.
>
> For the following SP
> Input can be:
>
> MemberId =10
>
> MemberProductId =12,14,35,67,89
>
>
> CREATE PROCEDURE dbo.insertMemberProductFavorite
> (
> @MemberId int,
> @MemberProductId varchar(100)
>
> )
> AS
> DECLARE @count int
> DECLARE @str VARCHAR(8000)
> DECLARE @spot SMALLINT
> WHILE @MemberProductId <> ''
> BEGIN
> SET @spot = CHARINDEX(',', @MemberProductId)
> IF @spot>0
> BEGIN
> SET @str = CAST(LEFT(@MemberProductId, @spot-1) AS INT)
> SET @MemberProductId = RIGHT(@MemberProductId,
> LEN(@MemberProductId)-@spot)
> END
> ELSE
> BEGIN
> SET @str = CAST(@MemberProductId AS INT)
> SET @MemberProductId = ''
> END
> SELECT @count=count(1) FROM MemberProductFavorite
> WHERE MemberProductId=@str
> AND MemberId=@MemberId
> IF @count =0
> BEGIN
> INSERT INTO MemberProductFavorite
> (MemberId,
> MemberProductId)
> VALUES
> ( @MemberId,
> @str)
> END
> END
> RETURN
> GO
>
> Enjoy Coding
>
> Thanks & Regards,
>
> Fouzan.Y.
>
>
>
> Ricardo Luceac wrote:
>
> Insert multiple rows with stored procedures
> 31-Jan-08
>
> Hi all...
>
> How can I pass multiple rows parameters for a stored procedure??
>
> For example:
>
> I have an order table and a orderitens table.
>
> I need to insert the order, and insert the orderitems with the
> id of teh order... But te orderitems will have more than 1...
>
>
> thx...
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
> Previous Posts In This Thread:
>
> On Thursday, January 31, 2008 5:33 AM
> Ricardo Luceac wrote:
>
> Insert multiple rows with stored procedures
> Hi all...
>
> How can I pass multiple rows parameters for a stored procedure??
>
> For example:
>
> I have an order table and a orderitens table.
>
> I need to insert the order, and insert the orderitems with the
> id of teh order... But te orderitems will have more than 1...
>
>
> thx...
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
> On Thursday, January 31, 2008 5:41 AM
> Tibor Karaszi wrote:
>
> Consider passing the order as XML and use OPENXML (if 2000) or .
> Consider passing the order as XML and use OPENXML (if 2000) or .Nodes (if
> 2005). You will find more
> info in general on this topic at www.sommarskog.se.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Ricardo Luceac" wrote in message
>
>
> On Thursday, January 31, 2008 5:43 AM
> Jack Vamvas wrote:
>
> Re: Insert multiple rows with stored procedures
> Two potential options:
> 1)Pass in as an array , but then you will need to split the array and run
> the relevant INSERT multiple times within the stored procedure
> 2)Run the stored procedure(s) multiple times from within your application
>
> Normally , I prefer option 2) , but this depends on the application
>
> --
>
> Jack Vamvas
> ___________________________________
> Search IT jobs from multiple sources- http://www.ITjobfeed.com
>
>
>
>
> "Ricardo Luceac" wrote in message
>
>
> On Thursday, January 31, 2008 6:17 AM
> novalidaddres wrote:
>
> Hi Ricardo,Nowadays using XML is the best option (better than comma
> separated
> Hi Ricardo,
>
> Nowadays using XML is the best option (better than comma separated values
> unless you have strong network bandwidth restrictions) . In SQL Server
> 2008
> you have table valued parameters that will solve your problem in a
> efficient
> and elegant way
>
> --
>
> Rub?n Garrig?s
> Solid Quality Mentors
>
> "Ricardo Luceac" wrote in message
>
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> ASP.NET Process Killer Revisited
> http://www.eggheadcafe.com/tutorials/aspnet/761a922d-7b70-4df6-9e23-4a...d7bc912
 >> Stay informed about: Insert multiple rows with stored procedures 

use SQL 2008 and lookup TVPs.

http://msdn.microsoft.com/en-us/library/bb522663.aspx

you can do something like this.
exec my_invoice_procedure NAME, DATE, INVOICE_NUMBER, TVPitems

TVPitems is a table that has rows of data where each row represents another
line item of the invoice.

Leon


<Fouzan Yoosuf> wrote in message ...
> Hi Friend,
>
> You can use the follwowing format of SP to insert multiple rows of data in
> a single SP.
>
> You can pass values as comma seperated values.
>
> For the following SP
> Input can be:
>
> MemberId =10
>
> MemberProductId =12,14,35,67,89
>
>
> CREATE PROCEDURE dbo.insertMemberProductFavorite
> (
> @MemberId int,
> @MemberProductId varchar(100)
>
> )
> AS
> DECLARE @count int
> DECLARE @str VARCHAR(8000)
> DECLARE @spot SMALLINT
> WHILE @MemberProductId <> ''
> BEGIN
> SET @spot = CHARINDEX(',', @MemberProductId)
> IF @spot>0
> BEGIN
> SET @str = CAST(LEFT(@MemberProductId, @spot-1) AS INT)
> SET @MemberProductId = RIGHT(@MemberProductId,
> LEN(@MemberProductId)-@spot)
> END
> ELSE
> BEGIN
> SET @str = CAST(@MemberProductId AS INT)
> SET @MemberProductId = ''
> END
> SELECT @count=count(1) FROM MemberProductFavorite
> WHERE MemberProductId=@str
> AND MemberId=@MemberId
> IF @count =0
> BEGIN
> INSERT INTO MemberProductFavorite
> (MemberId,
> MemberProductId)
> VALUES
> ( @MemberId,
> @str)
> END
> END
> RETURN
> GO
>
> Enjoy Coding
>
> Thanks & Regards,
>
> Fouzan.Y.
>
>
>
> Ricardo Luceac wrote:
>
> Insert multiple rows with stored procedures
> 31-Jan-08
>
> Hi all...
>
> How can I pass multiple rows parameters for a stored procedure??
>
> For example:
>
> I have an order table and a orderitens table.
>
> I need to insert the order, and insert the orderitems with the
> id of teh order... But te orderitems will have more than 1...
>
>
> thx...
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
> Previous Posts In This Thread:
>
> On Thursday, January 31, 2008 5:33 AM
> Ricardo Luceac wrote:
>
> Insert multiple rows with stored procedures
> Hi all...
>
> How can I pass multiple rows parameters for a stored procedure??
>
> For example:
>
> I have an order table and a orderitens table.
>
> I need to insert the order, and insert the orderitems with the
> id of teh order... But te orderitems will have more than 1...
>
>
> thx...
>
> *** Sent via Developersdex http://www.developersdex.com ***
>
> On Thursday, January 31, 2008 5:41 AM
> Tibor Karaszi wrote:
>
> Consider passing the order as XML and use OPENXML (if 2000) or .
> Consider passing the order as XML and use OPENXML (if 2000) or .Nodes (if
> 2005). You will find more
> info in general on this topic at www.sommarskog.se.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "Ricardo Luceac" wrote in message
>
>
> On Thursday, January 31, 2008 5:43 AM
> Jack Vamvas wrote:
>
> Re: Insert multiple rows with stored procedures
> Two potential options:
> 1)Pass in as an array , but then you will need to split the array and run
> the relevant INSERT multiple times within the stored procedure
> 2)Run the stored procedure(s) multiple times from within your application
>
> Normally , I prefer option 2) , but this depends on the application
>
> --
>
> Jack Vamvas
> ___________________________________
> Search IT jobs from multiple sources- http://www.ITjobfeed.com
>
>
>
>
> "Ricardo Luceac" wrote in message
>
>
> On Thursday, January 31, 2008 6:17 AM
> novalidaddres wrote:
>
> Hi Ricardo,Nowadays using XML is the best option (better than comma
> separated
> Hi Ricardo,
>
> Nowadays using XML is the best option (better than comma separated values
> unless you have strong network bandwidth restrictions) . In SQL Server
> 2008
> you have table valued parameters that will solve your problem in a
> efficient
> and elegant way
>
> --
>
> Rub?n Garrig?s
> Solid Quality Mentors
>
> "Ricardo Luceac" wrote in message
>
>
>
> Submitted via EggHeadCafe - Software Developer Portal of Choice
> ASP.NET Process Killer Revisited
> http://www.eggheadcafe.com/tutorials/aspnet/761a922d-7b70-4df6-9e23-4a...d7bc912
 >> Stay informed about: Insert multiple rows with stored procedures