Create a stored procedure named spInsertWorkRecord. Before inserting the record, it will test for the existence of the EmpID in the EmpData table. If the EmpID exists, the record will be inserted. If the EmpID does not exist, a custom RAISERROR message will be displayed. The sp should flow as follows: 

 

The input parameters for the stored procedure:

@PayRate (money) 

@Salaried (bit) 

@StartDate (smalldatetime) 

@EndDate (smalldatetime) 

@COLA (bit) 

@EmpID (int) 

@DeptID (int)

 

Test for the existence of the EmpID

 

If it exists, insert the values into the Work table 

 

If it doesn't exist, create RAISERROR that says "Not a valid EmployeeID" with a severity level of 11 and a state code of 1.

 

PART B: Create a script with a TRY/CATCH that executes spInsertWorkRecord. In the TRY, pass the following values into the procedure:

 

21.20, 0, '11/5/2014', NULL, 0, 83, 9

 

In the CATCH, print 2 statements:

• "An error occurred."
• "Message: "(custom error message from spInsertWorkRecord

Answer rating: 100% (QA)

Certainly Below is the SQL code to create the stored procedure spInsertWorkRecord and a script using ... View the full answer