I have found alot of help on this site, and is helping me along. I am having trouble with a stored procedure. This is my first stored procedure.
The task I was given was to create a stored procedure that will accept leave balances for employees from our web based leave system. Those 4 values are: @Annual_Forward , @Sick_Forward, @Family_Forward , @Other_Forward. All variables are not going to be passed, so allowing null/empty values is a necessity. That is no problem as I figured that part out. My problem is the Output parameters. I was told to return @SQLCode(This is the code returned by the SQL Server showing if successful or not.) and @ErrMsg(The message returned by the database when there is an error.)
This is my stored procedure I wrote so far:
I can execute the procedure doing this:
As you can tell it is very rudimentary. The exec statement works, but if a ssn is passed to the database that doesn't exist then obviously that should generate an error. So i think something using IF @@ROWCOUNT = 0 then show error.
The @SQLCode and @ErrMsg is the problem. I am sure those two will be used to generate an email in the application side to alert me if there are errors. I hope some can help, and also post where to find some help on the web or books the would help a true beginner.
Thanks.
The task I was given was to create a stored procedure that will accept leave balances for employees from our web based leave system. Those 4 values are: @Annual_Forward , @Sick_Forward, @Family_Forward , @Other_Forward. All variables are not going to be passed, so allowing null/empty values is a necessity. That is no problem as I figured that part out. My problem is the Output parameters. I was told to return @SQLCode(This is the code returned by the SQL Server showing if successful or not.) and @ErrMsg(The message returned by the database when there is an error.)
This is my stored procedure I wrote so far:
Code:
CREATE PROCEDURE [dbo].[usp_AcceptLeaveBalance] @Emp_SSN int, @Annual_Forward decimal(10,2), @Sick_Forward decimal(10,2), @Family_Forward decimal(10,2), @Other_Forward decimal(10,2) AS UPDATE OT_MAIN SET EmpAnnual_Forward = IsNull(@Annual_Forward, EmpAnnual_Forward), EmpSick_Forward = IsNull(@Sick_Forward, EmpSick_Forward), EmpFamily_Forward = IsNull(@Family_Forward, EmpFamily_Forward), EmpOther_Forward = IsNull(@Other_Forward, EmpOther_Forward) WHERE Emp_SSN=@Emp_SSN
Code:
exec usp_AcceptLeaveBalance 123456789, 10, null,null, null
The @SQLCode and @ErrMsg is the problem. I am sure those two will be used to generate an email in the application side to alert me if there are errors. I hope some can help, and also post where to find some help on the web or books the would help a true beginner.
Thanks.
Comment