Tag Archives: insert into multiple table

How to write store procedure to insert into multiple table at one time

Today just write a complex store procedures to retrieve data from one table, and insert  values into two tables

Here is my table: DataBase Table

My Task is write a store procedure to get username from tblUserAuthemtication And insert usename and other Values into tblLMStatus and tblJob table.

Here is my solution:


CREATE PROCEDURE [dbo].[spStoreLMStatusRecording]
@parameterRef varchar(30),
@parameterLM_Booking_Ref varchar(30),
@parameterLMStatus nvarchar(10),
@parameterEntry_datetime datetime

AS
BEGIN
declare
@username varchar(500),
@JobID int
insert into tblJob values(@parameterRef,@parameterLM_Booking_Ref);
select @username = username from tblUserAuthentication
select @JobID = ID from tblJob
insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@JobID,@username)

END

In my next post I will write how to use this store procedure in my Application project
And my task is not just use this I need write a event to use this store procedure and
also that button trick Send email at the same time.