CODEWITHSUNDEEP
sql-server-2008
Lee
Lee

Reputation: 31

SQL Server stored procedure for inserting multiple rows in one table and single row in other table

I am in need of a stored procedure for sales transaction. In a single SP I need to store CustomerID in one table and list of products purchased (multiple rows) in another table.

Can any one give me an best example?

Thanks in advance.

Upvotes: 0

Views: 5924

Answers (2)

George Botros
George Botros

Reputation: 4401

Table-Valued Parameters is a new feature introduced in SQL SERVER 2008. In earlier versions of SQL SERVER it is not possible to pass a table variable in stored procedure as a parameter, but now in SQL SERVER 2008 we can use Table-Valued Parameter to send multiple rows of data to a stored procedure or a function without creating a temporary table or passing so many parameters.

You can read about it here

for more information about using it with ado check this great article

SQL Server 2008 Table-Valued Parameters and C# Custom Iterators: A Match Made In Heaven!

Upvotes: 2

Ashkan Mobayen Khiabani
Ashkan Mobayen Khiabani

Reputation: 34152

well in the stored procedure you can use any many insert commands as you want in any table you want, as your question is not clear enough that i write the exact stored procedure you want, I'm writing an example.

use [databasename]
go
create procedure [dbo].[my_stored_procedure](@customerid int) as
begin
insert into [customerstable](customerid) values (@customerid)
insert into [someothertable](somefieldname1, somefieldname2) values(somefield1value, somefield2value)
insert into [someothertable2](somefieldname1, somefieldname2) values(somefield1value, somefield2value)

end

Upvotes: 0

Related Questions