abcool abcool - 1 month ago 19
SQL Question

Stored Procedure Insert Issues

I have a T-SQL stored procedure that inserts into six different tables in the same database.

e.g.:

Declare @...variables name
declare @newid as int
declare @Identityid as int

set @newid = 0
set @Identityid = 0

begin try
begin transaction

INSERT INTO dbo.TABLE1 (col1, column2)
VALUES (col1.values, col2.values)

SET @newid = @@IDENTITY

INSERT INTO dbo.TABLE2 (Table2_id, col1, column2)
VALUES (@newid, col1.values, col2.values) !--it's taking the same ID)

INSERT INTO dbo.TABLE3 (Table3_id, col1, column2)
VALUES(@newid, col1.values, col2.values)

INSERT INTO dbo.TABLE4 (Table4_id, col1, column2)
VALUES(@newid, col1.values, col2.values)

INSERT INTO dbo.TABLE5 (col1, column2)
VALUES (col1.values, col2.values)

SET @Identityid = @@IDENTITY (!--this IdentityID will be same for
next table)

INSERT INTO dbo.TABLE6 (Table6_id, col1, column2)
VALUES (@Identityid, col1.values, col2.values)

COMMIT TRANSACTION
END TRY
BEGIN CATCH
RAISERROR('ERROR',16,1)
ROLLBACK TRANSACTION
PRINT ERROR_MESSAGE()
END CATCH
END


Now I came across a situation where I need to insert multiple records in the last two tables (
Table5
and
Table6
).

I am calling this procedure from a web service (VB.NET)

Now I am spinning my wheels trying to figure out how I can insert multiple records only to the last two tables. How can I do this?

ANSWER:

Table-valued parameters. "Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL"

to see and follow the steps of Table-Valued parameters Click here

Answer

I got the answer:Table-valued parameters. "Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server without requiring multiple round trips or special server-side logic for processing the data. You can use table-valued parameters to encapsulate rows of data in a client application and send the data to the server in a single parameterized command. The incoming data rows are stored in a table variable that can then be operated on by using Transact-SQL"

to see and follow the steps of Table-Valued parameters Click here

Comments