[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Try Catch in a user function

Jeremy

3/24/2007 5:27:00 PM

Try-Catch blocks aren't allowed in user functions. What do you do instead?

I'm writing a function that casts the input as follows

declare @input as varchar(3)
cast (@input as integer)

I'd really like to gracefully handle cases where the input data is screwed
up.

Jeremy


6 Answers

jeevan

3/25/2007 3:18:00 AM

0

Validate the @input using LIKE before you do anything with it.

SET @value = CASE WHEN @input LIKE '[0-9][0-9][0-9]' THEN CAST (@input AS
INTEGER)
ELSE NULL END

"Jeremy" <jeremy-nospam@ninprodata.com> wrote in message
news:e9I9wmjbHHA.2552@TK2MSFTNGP06.phx.gbl...
> Try-Catch blocks aren't allowed in user functions. What do you do
> instead?
>
> I'm writing a function that casts the input as follows
>
> declare @input as varchar(3)
> cast (@input as integer)
>
> I'd really like to gracefully handle cases where the input data is screwed
> up.
>
> Jeremy
>


Tom Cooper

3/25/2007 4:22:00 AM

0

And allow for the case where @input is less than 3 characters long, like
'25'

SET @value = CASE WHEN Right('00' + @input, 3) LIKE '[0-9][0-9][0-9]'
THEN CAST (@input AS INTEGER)
ELSE NULL END

Tom

"Mike C#" <xyz@xyz.com> wrote in message
news:OXCJ8wobHHA.4888@TK2MSFTNGP06.phx.gbl...
> Validate the @input using LIKE before you do anything with it.
>
> SET @value = CASE WHEN @input LIKE '[0-9][0-9][0-9]' THEN CAST (@input AS
> INTEGER)
> ELSE NULL END
>
> "Jeremy" <jeremy-nospam@ninprodata.com> wrote in message
> news:e9I9wmjbHHA.2552@TK2MSFTNGP06.phx.gbl...
>> Try-Catch blocks aren't allowed in user functions. What do you do
>> instead?
>>
>> I'm writing a function that casts the input as follows
>>
>> declare @input as varchar(3)
>> cast (@input as integer)
>>
>> I'd really like to gracefully handle cases where the input data is
>> screwed up.
>>
>> Jeremy
>>
>
>


Jeremy

3/25/2007 4:23:00 PM

0

Gentlemen, thanks. After I posted, I remembered isNumeric(), which seems to
work wonderfully. Still, it would be good to have try/catch in the toolbox.

Jermey

"Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
news:OLGHXUpbHHA.2088@TK2MSFTNGP04.phx.gbl...
> And allow for the case where @input is less than 3 characters long, like
> '25'
>
> SET @value = CASE WHEN Right('00' + @input, 3) LIKE '[0-9][0-9][0-9]'
> THEN CAST (@input AS INTEGER)
> ELSE NULL END
>
> Tom
>
> "Mike C#" <xyz@xyz.com> wrote in message
> news:OXCJ8wobHHA.4888@TK2MSFTNGP06.phx.gbl...
>> Validate the @input using LIKE before you do anything with it.
>>
>> SET @value = CASE WHEN @input LIKE '[0-9][0-9][0-9]' THEN CAST (@input AS
>> INTEGER)
>> ELSE NULL END
>>
>> "Jeremy" <jeremy-nospam@ninprodata.com> wrote in message
>> news:e9I9wmjbHHA.2552@TK2MSFTNGP06.phx.gbl...
>>> Try-Catch blocks aren't allowed in user functions. What do you do
>>> instead?
>>>
>>> I'm writing a function that casts the input as follows
>>>
>>> declare @input as varchar(3)
>>> cast (@input as integer)
>>>
>>> I'd really like to gracefully handle cases where the input data is
>>> screwed up.
>>>
>>> Jeremy
>>>
>>
>>
>
>


Plamen Ratchev

3/25/2007 5:56:00 PM

0

Just be careful because ISNUMERIC returns 1 even if there are other
characters like [.,$+-] and all the monetary symbols in the string to
convert. Try to pass '$25' or '1.2' or '2,3'. All will be treated as valid
by ISNUMERIC, but the CAST to INTEGER will fail.

The approach described by Mike and Tom will work better. I can only add that
you can use something like this:
SET @value = CASE
WHEN LTRIM(@input) LIKE REPLICATE('[0-9]', LEN(LTRIM(@input))
THEN CAST(@input AS INTEGER)
ELSE NULL END

That way you can handle variable length input string (not limited to 3) and
it will take care when you have leading blanks (like in ' 25'). Based on
requirements you may enhance the logic to handle blank string '' and
negative numbers.

All that said, if the input is passed to SQL Server from a front interface
then it will be best to handle that validation on the client side. Most
languages have very good functions and capabilities to verify integer input,
plus it will save the round trip.

HTH,

Plamen Ratchev
http://www.SQL...

Plamen Ratchev

3/25/2007 6:15:00 PM

0

Oops, missed one ")":

SET @value = CASE
WHEN LTRIM(@input) LIKE REPLICATE('[0-9]', LEN(LTRIM(@input)))
THEN CAST(@input AS INTEGER)
ELSE NULL END

Plamen Ratchev
http://www.SQL...

jeevan

3/27/2007 4:59:00 AM

0

IsNumeric will return 1 if you include a sign (like "-100"), a decimal point
(like "10.8"), an exponent (like "6.3E+34") or even this: "10D9". Be
careful.

User-defined functions have special restrictions on them concerning "side
effects" in the database. The fact that you can't handle errors in them is
probably an extension of that prohibition. If you think you need the
error-handling functionality, consider using an SP instead.

"Jeremy" <jeremy-nospam@ninprodata.com> wrote in message
news:O2ZyhnvbHHA.596@TK2MSFTNGP06.phx.gbl...
> Gentlemen, thanks. After I posted, I remembered isNumeric(), which seems
> to work wonderfully. Still, it would be good to have try/catch in the
> toolbox.
>
> Jermey
>
> "Tom Cooper" <tomcooper@comcast.no.spam.please.net> wrote in message
> news:OLGHXUpbHHA.2088@TK2MSFTNGP04.phx.gbl...
>> And allow for the case where @input is less than 3 characters long, like
>> '25'
>>
>> SET @value = CASE WHEN Right('00' + @input, 3) LIKE '[0-9][0-9][0-9]'
>> THEN CAST (@input AS INTEGER)
>> ELSE NULL END
>>
>> Tom
>>
>> "Mike C#" <xyz@xyz.com> wrote in message
>> news:OXCJ8wobHHA.4888@TK2MSFTNGP06.phx.gbl...
>>> Validate the @input using LIKE before you do anything with it.
>>>
>>> SET @value = CASE WHEN @input LIKE '[0-9][0-9][0-9]' THEN CAST (@input
>>> AS INTEGER)
>>> ELSE NULL END
>>>
>>> "Jeremy" <jeremy-nospam@ninprodata.com> wrote in message
>>> news:e9I9wmjbHHA.2552@TK2MSFTNGP06.phx.gbl...
>>>> Try-Catch blocks aren't allowed in user functions. What do you do
>>>> instead?
>>>>
>>>> I'm writing a function that casts the input as follows
>>>>
>>>> declare @input as varchar(3)
>>>> cast (@input as integer)
>>>>
>>>> I'd really like to gracefully handle cases where the input data is
>>>> screwed up.
>>>>
>>>> Jeremy
>>>>
>>>
>>>
>>
>>
>
>