CODEWITHSUNDEEP
sqlsql-serverreporting-services
Bernard Walters
Bernard Walters

Reputation: 391

BEGIN TRY above Parameters

I'm wondering if there is a way for me to have a BEGIN / TRY above the arameters of a procedure.

Like this:

BEGIN TRY
ALTER PROCEDURE [dbo].[usp_get_Reports_Parameter_Portfolio]
    @StartDate date,
    @EndDate date
AS
DECLARE @ReportTable Table(

The reason I want this is because I raise a custom error via RAISERROR whenever the try fails.

The reason I need to have the try ABOVE the insertion of the parameters is because I want my custom message to appear when a parameter that is input is the wrong type.

Upvotes: 0

Views: 75

Answers (2)

FDavidov
FDavidov

Reputation: 3675

Any code within a stored procedure/function is reached after the stored procedure/function becomes properly invoked. As such, any exception handling mechanisms that you may wish to include within your stored procedure/function are not visible at the time of the invocation but only after such invocation was successful.

If the invocation takes place from a different system (e.g. a JAVA program), you should wrap the stored procedure/function invocation within the JAVA with the required TRY/CATCH pair.

If the invocation takes place from within the same database, the calling code should include the error handling piece.

Upvotes: 1

Serge
Serge

Reputation: 4036

No, this is not possible.

You must pass correct parameter types. If you need to, you can check parameter values immediately inside the stored procedure:

IF DATEDIFF(DAY, GETDATE(), @StartDate) > 100
    ;THROW 50000, '@StartDate is invalid', 1

Also, you can wrap your stored procedure call in a TRY - CATCH block.

Upvotes: 2

Related Questions