1

Based on the following code:

/* This SP takes a query and a unique temporary table name as input... 
The outputs is a string with the command to create the tmp table based on the query that is given in the @SRC parameter 
*/ 
EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
/* This executed the query that will create the tmp table and insert the data from @SRC */
EXEC(@SQLStr)

The whole stored procedure that calls these 2 lines (among many others) is in a Try Catch. I've just run into a problem where the query in @SRC had a syntax error which called the Catch portion:

BEGIN CATCH
        ROLLBACK TRANSACTION;
        SET @ERRORMESSAGE=CASE WHEN @ERRORMESSAGE='' THEN ERROR_MESSAGE() ELSE @ERRORMESSAGE END 
        RAISERROR (@ERRORMESSAGE,16,1)
END CATCH

the Try has many validations and I set @ErrorMessage to personalized messages in most cases. In this case, I would like to set a personalized message instead of getting the standard error message

(example): Incorrect syntax near 'FROM'.

I'm trying to figure out how I can get the EXEC(@SqlStr) return a successful or failure code, so I can set the @ErrorMessage before it goes into catch portion of the code.

1

I found the solution... I wrapped the statements in another try catch within the big try catch (Didn't know we can do that) :-(

    BEGIN TRY 
        EXEC dbo.createTmpFromQuery @SRC,@uniqueTMP,@createTmpStr=@SQLStr OUTPUT
        EXEC(@SQLStr)
    END TRY
    BEGIN CATCH 
        SET @ErrorMessage='Error Creating '+@uniqueTMP+' table - verify query in @SRC'
        RAISERROR(@ErrorMessage,16,1)
    END CATCH 

I tested it an it worked. I hope this will help someone else...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.