Insert multiple rows with stored procedures
in [Programming SQL]
|
From: Fouzan on 21 Dec 2009 04:01 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=(a)str AND MemberId=(a)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" <rluceac(a)gmail.com> wrote in message news:edMf3S$YIHA.1208(a)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" <rluceac(a)gmail.com> wrote in message news:edMf3S$YIHA.1208(a)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" <rluceac(a)gmail.com> wrote in message news:edMf3S$YIHA.1208(a)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
From: Uri Dimant on 21 Dec 2009 05:10 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:200912214127fouzone(a)hotmail.com... > 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=(a)str > AND MemberId=(a)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" <rluceac(a)gmail.com> wrote in message > news:edMf3S$YIHA.1208(a)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" <rluceac(a)gmail.com> wrote in message > news:edMf3S$YIHA.1208(a)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" <rluceac(a)gmail.com> wrote in message > news:edMf3S$YIHA.1208(a)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
From: Leon McCalla on 22 Dec 2009 11:47 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 news:200912214127fouzone(a)hotmail.com... > 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=(a)str > AND MemberId=(a)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" <rluceac(a)gmail.com> wrote in message > news:edMf3S$YIHA.1208(a)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" <rluceac(a)gmail.com> wrote in message > news:edMf3S$YIHA.1208(a)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" <rluceac(a)gmail.com> wrote in message > news:edMf3S$YIHA.1208(a)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
|
Pages: 1 Prev: loop between 2 datetime field values Next: How to narrow the selection |