Try catch fails inside a function - by Jose Blakeley

Status : 

 


43
0
Sign in
to vote
ID 132225 Comments
Status Active Workarounds
Type Bug Repros 28
Opened 6/12/2006 4:57:32 PM
Access Restriction Public

Description

When I create a function as follows:
create function f (@x int) returns int as 
	begin
		begin try
			set @x = 2
		end try
		begin catch
			set @x = 1
		end catch 
	return @x
	end
I get the following errors:
Msg 443, Level 16, State 14, Procedure f, Line 4
Invalid use of side-effecting or time-dependent operator in 'BEGIN TRY' within a function.
Msg 443, Level 16, State 14, Procedure f, Line 6
Invalid use of side-effecting or time-dependent operator in 'END TRY' within a function.
Msg 443, Level 16, State 14, Procedure f, Line 7
Invalid use of side-effecting or time-dependent operator in 'BEGIN CATCH' within a function.
Msg 443, Level 16, State 14, Procedure f, Line 9
Invalid use of side-effecting or time-dependent operator in 'END CATCH' within a function.

However, the similar body works inside a stored procedure. Why is the function reporting the above errors? What is the recommended way to use try/catch within a function?
Sign in to post a comment.
Posted by CesarFaria on 2/5/2016 at 4:22 AM
Hello Microsoft
Someone there?

I'm coming from future to say that in SQL Server 2014 th limitation still exists.
10 years and nothing?
At least say why...
Posted by Nitesh Rai on 2/2/2012 at 12:24 AM
MS,

This limitation still exists. When can we expect it to be fixed?

Regards,
Nitesh Rai
Posted by Joseph Foster on 8/15/2011 at 12:33 PM
I definitely agree with Robert...the error provided is inaccurate. This "restriction" should be removed...is there a plan in the books to remove this restriction later???
Posted by Robert Heinig II on 5/19/2011 at 8:27 AM
Five years later, and 2008 R2 CU7 still incorrectly thinks a TRY-CATCH would somehow be able to have side effects ouside of the UDF it's in. At least change the error message guys, the construct itself cannot conceivably have a side effect nor is it time-dependent.
Posted by Mustafa S. Ali on 11/5/2009 at 10:53 AM
Hi,

This is indeed a serious limitation and it breaks the whole logic of precise development. A UDF is supposed to do computation, and therefore needs TRY CATCH.

Regards,
Mustafa
Posted by Microsoft on 7/20/2006 at 2:23 PM
Hi,

Thank you for bringing this issue to our attention. SQL Server 2005 does not support TRY CATCH blocks inside functions. We are looking at removing this restriction in a future release.

Thank you,
Tomer Verona (tomerv@microsoft.com)
SQL Server Engine
[MSFT]
Posted by Razvan Socol on 6/12/2006 at 10:43 PM
Books Online documents this behaviour, in topic "CREATE FUNCTION (Transact-SQL)":
"The following statements are valid in a function: [...] Control-of-Flow statements except TRY...CATCH statements. [...]"

However, this is a severe limitation that should be removed in a future release. You should post a suggestion in this regard and I will wholeheartedly vote for it.