asked on

TRY / CATCH SYNTAX with a scalar function

I've been trying to incorporate the same TRY / CATCH code I use with sprocs in a scalar function. (See the code snippet.) No matter where I put the BEGIN TRY command and the END TRY / BEGIN CATCH commands the SQL compiler balks. For example:

Msg 102, Level 15, State 1, Procedure centigrade_to_farenheit, Line 6
Incorrect syntax near 'TRY'.
Msg 102, Level 15, State 1, Procedure centigrade_to_farenheit, Line 11
Incorrect syntax near 'BEGIN'.

Is it possible to combine TRY / CATCH with functions?

CREATE FUNCTION dbo.centigrade_to_farenheit (@centigrade float)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN TRY
BEGIN
     RETURN((@centigrade * 1.8) + 32.0)
END
END TRY
BEGIN CATCH
   INSERT INTO ErrorLog (ErrorDateTime, ErrorHint, ErrorNumber, ErrorLine, ErrorSeverity, ErrorState, ErrorMessage)
   VALUES (GETDATE(), @ErrorHint, ERROR_NUMBER(), ERROR_LINE(), ERROR_SEVERITY(), ERROR_STATE(),  ERROR_MESSAGE())   
END CATCH

Open in new window

Aneesh

Hello jdana,

I dont think you really need a TRY CATCH here

CREATE FUNCTION dbo.centigrade_to_farenheit (@centigrade float)
RETURNS float
WITH EXECUTE AS CALLER
AS
BEGIN
BEGIN TRY
RETURN((@centigrade * 1.8) + 32.0)
END TRY
BEGIN CATCH
INSERT INTO ErrorLog (ErrorDateTime, ErrorHint, ErrorNumber, ErrorLine, ErrorSeverity, ErrorState, ErrorMessage)
VALUES (GETDATE(), @ErrorHint, ERROR_NUMBER(), ERROR_LINE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE())
END CATCH
END

GOD BLESS,

Aneesh R.

ASKER CERTIFIED SOLUTION