Last post Jan 29, 2020 10:34 AM by PatriceSc
Jan 22, 2020 07:14 AM|demoninside9|LINK
Below is my store procedure.
INSERT INTO tblRequests
SET @request_id = SCOPE_IDENTITY();
INSERT INTO tblVendors
There was only one vendor for 1 request, it was working fine. I was doing both insertion in same sp because I need request_id = SCOPE_IDENTITY(); to insert in tblVendors table.
But now the requirement has been changed.
There may be multiple records for 1 request (tblRequests) into tblVendors
So I need to make another sp / case for inserting Vendors into tblVendors OR I can use loop here in the same sp.
I need to use a different approach? Because the about the vendors I am not sure there may be n number of vendors for 1 request.
So by placing a loop till the count of the controls on the page. Is it good because I already made a function to insert 1 vendor for 1 requests.
Jan 22, 2020 11:58 AM|imapsp|LINK
I believe you can use a table value parameter in the same procedure to insert vendors (1 or n):
Hope this help
Jan 29, 2020 10:03 AM|heera.chand|LINK
You can pass vendor details in XML format and convert XML into table using below example
declare @handle int
declare @XML xml = '<ROOT><ids><id>2013-01-01</id></ids><ids><id>2013-01-02</id></ids></ROOT>'
exec sp_xml_preparedocument @handle out, @XML
select * from openxml(@handle, '/ROOT/ids', 2) with (id Date)
exec sp_xml_removedocument @handle
Jan 29, 2020 10:34 AM|PatriceSc|LINK
Another option could be to split this SP. You could then return scope_identity to the client side and then call another SP for each tblVendors insertion all this inside a TransactionScope.
It would be the closest to using EF (that does support using SP and does all changes inside a transaction) if you consider that.