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.