Need a "stored something" that supports TRY/CATCH and being called from a SP like UDF

[mdlueck]

Need a "stored something" that supports TRY/CATCH and being called from a SP like UDF

I have been banging into limitations of what SQL Server 2008 R2 will not support.

I coded up a slick little UDF, debugged it in SSMS interactively, then tried installing the UDF only to find that TRY/CATCH blocks are not supported in UDF's.

I recoded in Stored Procedure (SP) style, only to receive a gripe trying to call that SP as if it were a UDF within the SELECT of another SP.

The reason I need to use TRY/CATCH is for these conversion UDF's, I was going to utilize the same sort of error checking as I did for these VBA validator functions:

Request for critique of my IsInt function - Generic Data Type Validation
http://www.access-programmers.co.uk/forums/showthread.php?t=241516&page=2#post1231644

That if the conversion fails for some reason, then the CATCH would supply a default/error return code, which would be the value the SELECT in the SP will return.

Suggestions of a way forward? My creativity is tired of being told a three letter answer... "NO!" :banghead:

 

[mdlueck]

Re: Need a "stored something" that supports TRY/CATCH and being called from a SP like

My SQL Server mentor friend provided an elegant one-liner solution to further validate the input is compatible with a smallint data type.

TRY/CATCH is no longer needed. Solved!

      -- Test of the value is even a number
      IF ISNUMERIC(@strInput)= 1
        BEGIN
          -- Test if the value is a valid smallint
          -- Thank you to Paul Nielsen for suggesting the next LOC!
          [B]SELECT @sintTest = CAST(CONVERT(NUMERIC(5,2), @strInput) AS smallint)[/B]
          -- Test if the conversion was able to transfer all characters from @strInput
          IF (@strInput = CAST(@sintTest AS varchar(3)))
            -- Test if the value is within range
            IF (@sintTest >= 1) AND (@sintTest <= 100)
              SET @sintRC = @sintTest
            ELSE
              SET @sintRC = -2
          ELSE
            SET @sintRC = -2
        END
      ELSE
        SET @sintRC = -2