February 19, 2008 at 2:01 pm
I understand the basic idea/use of TRY…CATCH. But I am not sure exactly where to use it in the overall sproc. If the sproc requires an EmployeeNumber, SSN, and LastName, does each of these parameters require it’s own TRY…CATCH, TRANS/COMMIT/ROLLBACK and RAISERROR code? Or, does one TRY…CATCH, etc… cover the whole sproc? (Example below.)
It seems to me that if I want the sproc to immediately inform the client App, let’s say ASP, of a problem with each required parameter, then each parameter should have it’s own separate TRY…CATCH, etc. code. But I also do not know what type of error handling ASP has.
If the answer to this Q is to give each parameter its own TRY…CATCH, etc., then I am assuming that each parameter will need it’s own custom error message.
--Validation of entries begins.
BEGIN TRY
BEGIN TRANSACTION
IF @EmployeeNumber is null or len(@EmployeeNumber) <> 4
begin
set @Message = 'Valid Employee Number Required'
set @continue = 0
end
--validate Social Security Number
If @SocialSecurity is null or len(@SocialSecurity) <> 9
begin
set @Message = 'Valid Social Security Number Required'
Set @continue = 0
end
--validate employee Last name is not Null or empty
If @LastName is null or len(@LastName) = 0
begin
set @Message = 'Client Name Required'
Set @continue = 0
end
---the above are required.
--To keep this example short, let’s assume that the first name and address are optional.
If len(@FirstName) = 0
Set @FirstName = NULL
--validate Address1.
if len(@Address1) = 0
set @Address1 = null
---There may be other req’d and not req'd parameters in this area of code.
COMMIT ---no errors in the req’d parameters above.
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
DECLARE @ErrMSg varchar(1000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1) ---NOTIFY ASP CLIENT APP
END CATCH --if all is OK, begin insert
IF @continue = 0
begin
return 1
end
else
IF @Employee_PK_ID is null or @Employee_PK_ID = 0
begin
insert into tbl_EmployeeBasicInformation
(EmployeeNumber,
SocialSecurity,
LastName,
FirstName,
Address1,
…………
)
values
(@EmployeeNumber,
@SocialSecurity,
upper(@LastName),
upper(@FirstName),
upper(@Address1),
……….
)
set @Employee_PK_ID = SCOPE_IDENTITY()
set @Message = 'Success'
end
If anyone sees any problems with the above code, please feel free to comment.
Thanks,
Bill
February 19, 2008 at 2:11 pm
I personally do not think that the validation you are doing in the example should be done in the UI or business layer. The reason for this is that you do not want a round trip to your SQL Server with invalid parameters. Particularly in the example you give, you kow the length of Employee and Social Security numbers so check them in UI.
In the SP your parameters should be defined correctly. In your example @EmployeeNumber should be defined as char(4) and @SocialSecurity as Char(9) with no defaults and then if the data does not match the definition the caller will get the error.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 19, 2008 at 2:27 pm
It looks to me that your usage is mostly wrong. Your try does some testing, but does nothing that would raise an error (which is what would instantiate the CATCH), which is where all of the "work" that might cause a problem (and an error) lives. It looks to me that your CATCH would never actually fire.
Besides - the CATCH should ONLY fire when an application error is raised. You really don't want to use it as a fancy IF..THEN...ELSE construct.
Jack also has a point, although I like validation, so I'd do the validation here IN ADDITION to the UI stuff (just in case my UI folks have a bad day). This wouldn't however get them off the hook as to their validation.
Quite honestly - I'd probably reverse the 2. Put in INSERT into the TRY, put the IF statements in the CATCH. The IF's would only fire if the INSERT fails, telling me why.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 19, 2008 at 2:38 pm
I agree with Jack. The variables should be handled through the app. Additionally, in the try catch you are declaring a transaction; however, none of the code in the try catch is logged. All you are doing is setting variables to values. Your try catch should protect the insert statements for anomalies that could occur.
February 19, 2008 at 5:24 pm
That's the kind of direction I was hoping for. Thanks everyone.
Bill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply