Throw exception from SQL Server function to stored procedure

Question

I have stored procedure in SQL Server 2012 say spXample and a scaler-valued function say fXample. I call a function fXample from spXample. Can I throw an exception in function and catch it in stored procedure's Catch block and rethrow to the calling C# code?

Update:

The function I wrote like:

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 am getting error

Msg 443, Level 16, State 14, Procedure fXample, Line 46 Invalid use of a side-effecting operator 'THROW' within a function.

How do I write alternative code to achieve above functionality?

Answer 1

You can do this by forcing an error condition when your validation fails, provided that isn't a possible error that might occur naturally. When you know a certain error can only occur when validation has failed, you can handle that in a custom way by checking for that 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 it out:

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, Procedure spXample, Line 12
Your custom error message.

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

Answer 2

/*  *** EXAMPLES ***
    SELECT dbo.fnDoSomething(500) -- RETURNS TRUE
    
    SELECT dbo.fnDoSomething(5000) -- THROWS ERROR
        Msg 245, Level 16, State 1, Line 4
        Conversion failed when converting the varchar value 
        'Function Error - [dbo].[fnDoSomething] - Value is greater than 1000' to data type bit.
*/
CREATE FUNCTION dbo.fnDoSomething
(
    @SomeValue      INT
)
RETURNS BIT
AS
BEGIN
    IF @SomeValue > 1000
    BEGIN
        DECLARE @FunctionName AS SYSNAME = QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID))
        DECLARE @Error AS VARCHAR(200) = 'Function Error - '+ @FunctionName + ' - Value is greater than 1000'
        RETURN  CONVERT(BIT,@Error)
    END

    RETURN 1
END
GO