THROW statement in catch block

Question

I need to return error details in calling application using SQL stored procedure output parameter. If I use throw in catch block that time in case if procedure not execute successfully then output parameter will not not set with error details and if I ignore throw then it will work.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[pt44]
(
    @a int,
    @b int,
    @Msg varchar(2000) output
)
as
begin
    DECLARE @c int
    begin try
        set @c= (@a+@b)
        print 'C Value :' +@c--cast(@c as varchar(20))  error is here
    end try
    begin catch
        set @Msg='Error No. '+CAST(ERROR_NUMBER() AS VARCHAR(200))+'Error Line. '+CAST(ERROR_LINE() AS VARCHAR(200))+' Error Message.'+ERROR_MESSAGE()  
        --;throw
    end catch
end

Please tell me the best way how I can pass error to calling application and also want to use throw in SQL Server catch block.

The information you are trying to pass back via the output parameter is available by the exception if you pass it back to the application. — Dale K, Feb 20 '20 at 08:57
I am using vb 6.0 application at front end so there is no try catch block — TusharK, Feb 20 '20 at 09:54
are you looking for RAISERROR? https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver15 — Mehmet, Feb 20 '20 at 11:37

score 0 · Answer 1 · answered Feb 20 '20 at 12:14

0

Without structured error handling in VB 6, one can use On Error to execute error handling code and capture SQL error details. See the VB6 documentation (no longer maintained) for more information and examples.

On Error GoTo ErrorHandler

' execute proc here
oCmd.Execute

' happy path code here
GoTo Done

:ErrorHandler
' this will show SQL error
MsgBox Err.Description

:Done

This is the boiler plate T-SQL catch block code I normally use:

BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;
END;

answered Feb 20 '20 at 12:14

Dan Guzman

43,250
3
46
71

structured error handling in VB 6 some time not able to handle all errors which will occur in back end while executing stored procedure so that the reason i need proper solution – TusharK Feb 21 '20 at 07:57
@TusharK, you should be able to get all output, including errors, from any SQL query or proc in VB6. However, the VB code must consume all result returned (e.g. Command.NextRecordset). A common practice is to include `SET NOCOUNT ON;` in scripts and procs to ensure rowcount messages don't interfere with application code that doesn't expect them. – Dan Guzman Feb 21 '20 at 10:21

THROW statement in catch block

1 Answers1