Throw exception from SQL Server function to stored procedure

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

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.