Tag Archives: store procedure

A store procedure to insert values into multitable without duplicate records

The previous store procedure have records repeated and here is the improve one.

the table like this:

tables

 


  
CREATE PROCEDURE [dbo].[spStoreLMStatusRecording]       
@parameterJobID varchar(30),  
@parameterLMStatus nvarchar(10),  
@parameterEntry_datetime datetime   
   
AS  
BEGIN  
declare   
 @username varchar(500),  
 @Job_Acceptance bit   
    SET @Job_Acceptance = '1'  
   
   
 IF NOT EXISTS(SELECT * FROM tblJob where JobID = @parameterJobID)    
 begin   
  insert into tblJob values(@parameterJobID,@Job_Acceptance);  
  select @username = username from tblUserAuthentication    
  insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)  
 end  
 else  
 begin  
  select @username = username from tblUserAuthentication    
  insert into tblLMStatus values(@parameterLMStatus,@parameterEntry_datetime,@parameterJobID,@username)  
 end  
   
END  
Advertisements