Avatar of aninec
aninec

asked on 

Stored Procedure with IF Then Statement

The code for my sp shown below works so far.  I need to add some other elements.
1.  I need an IF Then to insert into a third table.  The variable is the EventType that I am sending in.  I have never done this before and I am not sure how its formated and where it goes (between current Begin End?)
2.  I need to write the scope identity from the Person and SafetyEvent tables to this third table.
3. I need to return the scope identity from the Saftey Event table to my applicaiton.  Note that this is working in the current code.


CREATE PROCEDURE spInsertEvent
 
	@Name		             VARCHAR(50),
	@DOB			DATETIME,
	@Status 		VARCHAR(10),
	@AcctNbr 		VARCHAR(15),
	@AdmitDate 		DATETIME,
	@Location		VARCHAR(50), 
	@Unit			VARCHAR(15), 
	@Fac			VARCHAR(50),  
	@PType		VARCHAR(20),  
 	@EventType		VARCHAR(50),
	@ReportedBy		VARCHAR(50),
	@EventDate		DATETIME,
	@EventTime		VARCHAR(5),
	@EventLocation	VARCHAR(50),
	@EventFac		VARCHAR(50),
	@EventDept		VARCHAR(50),
	@ReportStatus		VARCHAR(15),
	@MedicalDevice	VARCHAR(1),
	@ReporterPhone	VARCHAR(15),
	@ReporterDept	             VARCHAR(50),
	@MgrNotified		VARCHAR(50),
	@Description		VARCHAR(500)
	
	
	
	
 
	AS	
	SET NOCOUNT ON
	DECLARE @PersonID INT	
	DECLARE  @EventID INT
 
	BEGIN
	  INSERT INTO Person
	  	(Name, DOB, Status, AccountNbr, AdmitDate, Location, MedicalRecordNbr,Facility,PType)
		VALUES(@Name, @DOB, @Status, @AcctNbr,@AdmitDate, @Location, @Unit,@Fac, @PType)
		 SELECT @PersonID=@@Identity
	 
	  INSERT INTO SafetyEvent
	  	(PersonNbr, EventType, ReportedBy, EventDate, EventTime, Location, Facility, Dept, SafetyReportStatus, MedicalDevice, ReporterPhone, ReporterDept, ManagerNotified, Description)
		VALUES(@PersonID, @EventType, @ReportedBy, @EventDate, @EventTime, @EventLocation, @EventFac, @EventDept, @ReportStatus, @MedicalDevice, @ReporterPhone, @ReporterDept, @MgrNotified, @Description)
	             SELECT @EventID = SCOPE_IDENTITY() 
		  RETURN @EventID
	END
GO

Open in new window

SQL

Avatar of undefined
Last Comment
Dimitris

8/22/2022 - Mon