Error and output variables in stored procedures | Member | | Join Date: Dec 2007
Posts: 45
| |
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: - 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
I can execute the procedure doing this: - exec usp_AcceptLeaveBalance 123456789, 10, null,null, null
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.
|  | Expert | | Join Date: Jun 2007
Posts: 1,925
| | | re: Error and output variables in stored procedures
Quote:
Originally Posted by barmatt80 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:
- 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
I can execute the procedure doing this: - exec usp_AcceptLeaveBalance 123456789, 10, null,null, null
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.
did not test this but i hope you'll get the idea: -
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),
-
@result varchar(50) OUTPUT
-
AS
-
declare @errornum int, @rowupdated int
-
-
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
-
-
select @errornum = @@error, @rowupdated = @@rowcount
-
-
if @errornum <> 0
-
set result = 'Error updating table (' + cast(@errornum as varchar) + ')'
-
else
-
if @rowupdated > 0
-
set @result = 'Total records updated: ' + cast(@rowupdated as varchar)
-
else
-
set @result = 'No records updated'
-
-
go
-
-
-
declare @resultofproc varchar
-
-
exec usp_AcceptLeaveBalance 123456789, 10, null,null, null, @resultofproc OUTPUT
here's a good reference
you can also use 2 output parameters to pass both the errornumber and the message. you can get it from the sysmessages system table....
also, am not sure if you pass an existing ssn and all the other values are the same, meaning what your passing and the existing value on the table are the same, am not sure if it will effect in the @@rowcount...
happy coding...
-- CK
|  |
Similar Microsoft SQL Server bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,533 network members.
|
