Edit Question - KnowledgeHub

Title Body

I have a stored procedure that does some parameter validation and should fail and stop execution if the parameter is not valid. My first approach for error checking looked like this: create proc spBaz ( @fooInt int = 0, @fooString varchar(10) = null, @barInt int = 0, @barString varchar(10) = null ) as begin if (@fooInt = 0 and (@fooString is null or @fooString = '')) raiserror('invalid parameter: foo', 18, 0) if (@barInt = 0 and (@barString is null or @barString = '')) raiserror('invalid parameter: bar', 18, 0) print 'validation succeeded' -- do some work end This didn't do the trick since severity 18 doesn't stop the execution and 'validation succeeded' is printed together with the error messages. I know I could simply add a return after every raiserror but this looks kind of ugly to me: if (@fooInt = 0 and (@fooString is null or @fooString = '')) begin raiserror('invalid parameter: foo', 18, 0) return end ... print 'validation succeeded' -- do some work Since errors with severity 11 and higher are caught within a try/catch block another approach I tested was to encapsulate my error checking inside such a try/catch block. The problem was that the error was swallowed and not sent to the client at all. So I did some research and found a way to rethrow the error: begin try if (@fooInt = 0 and (@fooString is null or @fooString = '')) raiserror('invalid parameter: foo', 18, 0) ... end try begin catch exec usp_RethrowError return end catch print 'validation succeeded' -- do some work I'm still not happy with this approach so I'm asking you: How does your parameter validation look like? Is there some kind of "best practice" to do this kind of

Tags (comma-separated) Cancel