Trapping SP Input Param Error

Question

Trapping SP Input Param Error

coco286

SSC Enthusiast

Points: 195
More actions
November 25, 2003 at 9:40 am

#81564

Is it possible to trap an error generated by trying to assign an invalid value to a stored procedure input value...
Example...
CREATE PROC MyProc (
@MyVal smallint --My max value is 32767
) AS
--Do my stuff
GO
Call: EXEC MyProc 45000
How do I trap the error that is going to be generated when I execute this Stored Proc?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

mia Right there with Babe Points: 749 More actions · Answer 1

I think all you can really do here is check @@error to see if it is non-zero after the procedure call and then use the value of @@error to interrogate sysmessages and find out what the error is & whether its related to the stored procedure parameter.

If you know what MyProc expects, is it not possible to check the value of the parameter being passed to MyProc before you call the SP?

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

coco286 SSC Enthusiast Points: 195 More actions · Answer 2

mia,

yes i can check from the actual calling application.. however, then the business

rules will be outside of the stored proc which is what i'm trying to avoid.

i can't capture the @@ERROR after the proc runs because it bombs out the moment it can't convert the value that I'm passing to the smallint parameter.

mia Right there with Babe Points: 749 More actions · Answer 3

I guess the app is bombing out because the data type conversion error has severity level 16.

If it's possible for a value greater than smallint can hold to be passed to MyProc as a parameter, could you change the data type accepted by MyProc to int, then check the value is not greater than the max allowable value for smallint before you do anything in the procedure? It's a bit of a fudge I suppose, but at least MyProc could then return -1 as an error indication and exit gracefully instead of the whole thing bombing out, and it would leave the restriction to 32767 at the stored procedure level rather than the app level. Can't think of another way to get round the problem I'm afraid!

mia

ps see mom's code sample below for exactly what I mean - thanks mom

Edited by - mia on 11/25/2003 10:34:11 AM

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 4

CREATE PROC MyProc (

@MyVal int --My max value is 32767

) AS

--let's enforce your business rule here instead of on top level

if (@myval between 0 and 32767)

begin

--Do my stuff

end

else Print 'Please enter value between 0 and 32767'

return

GO

mom

coco286 SSC Enthusiast Points: 195 More actions · Answer 5

Thanks, momma mia.. hehe...

funny that those two names would respond..

I realize both of you said the same thing.

Unless I see something else, I'm going to

have to go with your suggestion.

mia Right there with Babe Points: 749 More actions · Answer 6

hadn't noticed the name thing...

...now I really know

Now I have an abba song in my head, which is no bad thing I guess!

Cheers,

mia

Cheers,
mia

Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.