Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 8583
  • Last Modified:

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

0
jdana
Asked:
jdana
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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.
0
 
HoggZillaCommented:
The TRY&CATCH construct cannot be used in a user-defined function.
http://msdn.microsoft.com/en-us/library/ms175976.aspx 
0
 
BrandonGalderisiCommented:
I believe you also can't do DML inside a function.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
jdanaAuthor Commented:
Brandon - What is DML?
0
 
Anthony PerkinsCommented:
DML: Data Manipulation Language.

DML statements: Insert, Update & Delete
0
 
jdanaAuthor Commented:
Thanks guys.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now