This was my procedure USE AdventureWorks2019 GO ALTER PROC Sales.p_InsertNewPay @BusinessEntityID INT, @RateCha
Posted: Sun Jul 10, 2022 11:25 am
This was my procedure
USE AdventureWorks2019 GOALTER PROC Sales.p_InsertNewPay @BusinessEntityID INT, @RateChangeDate DATETIME, @Rate MONEY, @PayFrequency TINYINT = 2
ASBEGIN SELECT PayFrequency FROMHumanResources.EmployeePayHistory WHERE PayFrequency = CASEWHEN @PayFrequency IS NULL THEN 2 ELSE @PayFrequencyEND IF @RateChangeDate IS NULL BEGIN SET@RateChangeDate = GETDATE () END
IF NOT EXISTS (SELECT * FROMHumanResources.EmployeePayHistory WHERE @BusinessEntityID<> BusinessEntityID) -- CHECK THE OUTPUT HERE IF <> ANDNOT EXISTS ARE REDUNDANT ELSE CHANGE <> TO = BEGIN PRINT'BusinessEntityID Does not exist, please input a validnumber' RETURN END
IF ((@PayFrequency <> 1) OR(@PayFrequency <> 2)) BEGIN PRINT'Invalid number. Can only choose 1 or 2' RETURN --stop procedure from running END ELSE BEGIN INSERT INTOHumanResources.EmployeePayHistory ( BusinessEntityID,RateChangeDate, Rate, PayFrequency ) VALUES (@BusinessEntityID,@RateChangeDate, @Rate, @PayFrequency ) ENDEND
How do i:
Add TRY...CATCH blocks to the procedures you created in Steps 1and 2. The procedure should log error messages to a new ErrorLog(create your own) table and rollback the transaction if an erroroccurs. The table should have fields for the ErrorNumber,ErrorMessage, LineNumber and Procedure.
USE AdventureWorks2019 GOALTER PROC Sales.p_InsertNewPay @BusinessEntityID INT, @RateChangeDate DATETIME, @Rate MONEY, @PayFrequency TINYINT = 2
ASBEGIN SELECT PayFrequency FROMHumanResources.EmployeePayHistory WHERE PayFrequency = CASEWHEN @PayFrequency IS NULL THEN 2 ELSE @PayFrequencyEND IF @RateChangeDate IS NULL BEGIN SET@RateChangeDate = GETDATE () END
IF NOT EXISTS (SELECT * FROMHumanResources.EmployeePayHistory WHERE @BusinessEntityID<> BusinessEntityID) -- CHECK THE OUTPUT HERE IF <> ANDNOT EXISTS ARE REDUNDANT ELSE CHANGE <> TO = BEGIN PRINT'BusinessEntityID Does not exist, please input a validnumber' RETURN END
IF ((@PayFrequency <> 1) OR(@PayFrequency <> 2)) BEGIN PRINT'Invalid number. Can only choose 1 or 2' RETURN --stop procedure from running END ELSE BEGIN INSERT INTOHumanResources.EmployeePayHistory ( BusinessEntityID,RateChangeDate, Rate, PayFrequency ) VALUES (@BusinessEntityID,@RateChangeDate, @Rate, @PayFrequency ) ENDEND
How do i:
Add TRY...CATCH blocks to the procedures you created in Steps 1and 2. The procedure should log error messages to a new ErrorLog(create your own) table and rollback the transaction if an erroroccurs. The table should have fields for the ErrorNumber,ErrorMessage, LineNumber and Procedure.