Insert multiple rows with stored procedures
Ricardo Luceac
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 ***
Jack Vamvas
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" <rlu...@gmail.com> wrote in message
news:edMf3S$YIHA...@TK2MSFTNGP05.phx.gbl...
Tibor Karaszi
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" <rlu...@gmail.com> wrote in message news:edMf3S$YIHA...@TK2MSFTNGP05.phx.gbl...
>
Rubén Garrigós
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" <rlu...@gmail.com> wrote in message
news:edMf3S$YIHA...@TK2MSFTNGP05.phx.gbl...
>
fouzanyoosuf
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...
For example:
thx...
Previous Posts In This Thread:
On Thursday, January 31, 2008 5:33 AM
Ricardo Luceac wrote:
Insert multiple rows with stored procedures
Hi all...
For example:
thx...
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" <rlu...@gmail.com> wrote in message news:edMf3S$YIHA...@TK2MSFTNGP05.phx.gbl...
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" <rlu...@gmail.com> wrote in message
news:edMf3S$YIHA...@TK2MSFTNGP05.phx.gbl...
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" <rlu...@gmail.com> wrote in message
news:edMf3S$YIHA...@TK2MSFTNGP05.phx.gbl...
Submitted via EggHeadCafe - Software Developer Portal of Choice
ASP.NET Process Killer Revisited
http://www.eggheadcafe.com/tutorials/aspnet/761a922d-7b70-4df6-9e23-4a372d7bc912/aspnet-process-killer-re.aspx
Uri Dimant
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 news:200912214...@hotmail.com...
sloan
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
........
<Fouzan Yoosuf> wrote in message news:200912214...@hotmail.com...
Leon McCalla
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 news:200912214...@hotmail.com...