Throw an exception from a SQL Server function in a stored procedure

Question

I have a stored procedure in SQL Server 2012, say spXample and a scaling function called fXample. I call the fXample function from spXample. Can I make an exception in a function and catch it in the Catch block and rethrow stored procedure on the calling C # code?

Update:

The function I wrote as:

 CREATE FUNCTION dbo.fXample(@i INT) RETURNS TINYINT AS BEGIN RETURN (SELECT CASE WHEN @i < 10 THEN THROW 51000,'Xample Exception',1; ELSE (SELECT @i) END); END GO 

I get an error

Msg 443, Level 16, State 14, Procedure fXample, Line 46. Invalid use of the side operator "THROW" inside a function.

How to write alternative code to achieve the above functions?

Answer 1

You can do this by forcing an error condition when your validation fails, provided that it is not a possible error that can occur naturally. When you know that a certain error can only occur if the verification fails, you can handle it in your own way by checking this error_number in your catch block. Example in tempdb:

 USE tempdb; GO CREATE FUNCTION dbo.fXample(@i INT) RETURNS TINYINT AS BEGIN RETURN (SELECT CASE WHEN @i < 10 -- change this to your "validation failed" condition THEN 1/0 -- something that will generate an error ELSE (SELECT @i) -- (you'd have your actual retrieval code here) END); END GO CREATE PROCEDURE dbo.spXample @i INT AS BEGIN SET NOCOUNT ON; BEGIN TRY SELECT dbo.fXample(@i); END TRY BEGIN CATCH IF ERROR_NUMBER() = 8134 -- divide by zero BEGIN THROW 50001, 'Your custom error message.', 1; -- you can throw any number > 50000 here END ELSE -- something else went wrong BEGIN THROW; -- throw original error END END CATCH END GO 

Now try:

 EXEC dbo.spXample @i = 10; -- works fine EXEC dbo.spXample @i = 6; -- fails validation EXEC dbo.spXample @i = 256; -- passes validation but overflows return 

Results:

 ---- 10 

Msg 50001, Level 16, State 1, spXample Procedure, Line 12
Your custom error message.

Msg 220, Level 16, State 2, spXample Procedure, Line 7
Arithmetic overflow error for tinyint data type, value = 256.