I need to use loop in SQL OR in my code file

Question

User1052662409 posted

Hi All,

Below is my store procedure.

INSERT INTO tblRequests
(employee_id, 
 country_id, 
 demand, 
 description, 
 remarks, 
 date_added, 
 request_number, 
 STATUS, 
 beneficiary_name, 
 estimated_cost, 
 request_for, 
 neutral_specification, 
 date_of_delivery, 
 type_of_request, 
 executed_country_id, 
 currency, 
 information_email, 
 approving_person, 
 last_updated_employee_id, 
 doc1, 
 doc2, 
 role_id
)
VALUES
(@employee_id, 
 @country_id, 
 @demand, 
 @description, 
 @remarks, 
 GETDATE(), 
 'Draft File', 
 @status, 
 @beneficiary_name, 
 @estimated_cost, 
 @request_for, 
 @neutral_specification, 
 @date_of_delivery, 
 @type_of_request, 
 @executed_country_id, 
 @currency, 
 @information_email, 
 @approving_person, 
 @last_updated_employee_id, 
 @doc1, 
 @doc2, 
 @role_id
);
SET @request_id = SCOPE_IDENTITY();
INSERT INTO tblVendors
(request_id, 
 name, 
 email, 
 phone, 
 address, 
 document1
)
VALUES
(@request_id, 
 @name, 
 @email, 
 @phone, 
 @address, 
 @document1
);

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.

OR

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.

Please suggest. 

Answer 1

User452040443 posted

Hi,

I believe you can use a table value parameter in the same procedure to insert vendors (1 or n):

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-ver15

Hope this help

Answer 2

User49201036 posted

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

Answer 3

User753101303 posted

Hi,

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.