I am trying to isolate an issue (that resembles the topic in stackoverflow.com/q/483787/537284). The issue involves a stored procedure and occurs "randomly" once a week. To try and reproduce the issue, I created different procedure versions to mimic good and bad possible outcomes:
- Good multi-row single resultset.
- Good multi-row single resultset with informational message.
- Good multi-row single resultset with Raiserror (less than level 11).
- Good multi-row single resultset with Print.
- Good empty single resultset.
- Bad syntax error.
- Bad syntax error with try/catch.
- Bad error with Raiserror (level 11).
- Bad error with Raiserror (level 11) and try/catch.
Between these tests, the syntax error with try/catch version behaved differently than what I would expect. Two resultsets (one empty and the other from the catch instruction) come back.
Does the syntax error get partially executed? I expected the catch block's result and not the try. I compared this with a Raiserror and using severity 11, it triggers the catch block with only one resultset returned. What is the difference between the syntax error and the Raiserror?
Here is my test procedure:
AS
BEGIN
SET NOCOUNT ON
SET ANSI_WARNINGS ON
SET IMPLICIT_TRANSACTIONS OFF
SET XACT_ABORT OFF
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
--RAISERROR ('goes to message tab yes?', 11, 1) WITH NOWAIT
SELECT '1' [myfield] FROM test_fulltext (nolock) WHERE CONTAINS(Command,'a monkey')
RETURN 0
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() [ErrorNumber]
END CATCH
RETURN -9999
END
Here is my test table (ripped from others):
CREATE TABLE test_fulltext
(
SPID INT NOT NULL,
Status VARCHAR(32) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(32) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(14) NULL,
ProgramName VARCHAR(32) NULL,
SPID2 INT
)
CREATE UNIQUE INDEX fulltextui ON test_fulltext(SPID);
CREATE FULLTEXT CATALOG fulltextft AS DEFAULT;
CREATE FULLTEXT INDEX ON test_fulltext(Command) KEY INDEX fulltextui;
