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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s