Throw exception from SQL Server function to stored procedure
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.
Although this has been solved but I feel I should still share this. As I think it will be a simpler solution:
BEGIN TRY
THROW 60000, 'Error', 1;
END TRY
BEGIN CATCH
THROW
END CATCH