Nice article. On a related note do you have any ideas on how to detect whether a stored procedure completed execution when run from ADO?

For instance take the following SQL;

CREATE TABLE TestTable (

x INT NOT NULL

CONSTRAINT CK_TestTable_x CHECK(x = 1)

)

GO

CREATE PROCEDURE TestSP

AS

BEGIN

BEGIN TRANSACTION

INSERT INTO TestTable (x) VALUES (2)

IF @@ERROR <> 0

BEGIN

ROLLBACK TRANSACTION

RETURN -1

END

INSERT INTO TestTable (x) VALUES (1)

IF @@ERROR <> 0

BEGIN

ROLLBACK TRANSACTION

RETURN -2

END

COMMIT TRANSACTION

RETURN 0

END

GO

and the following VB program;

Dim conn As Connection

Dim cmd As Command

Dim prm As Parameter

Set conn = New Connection

Set cmd = New Command

Call conn.Open("File Name=c:\localhost.udl")

cmd.CommandType = adCmdStoredProc

cmd.CommandText = "TestSP"

Set cmd.ActiveConnection = conn

Set prm = cmd.CreateParameter("", adInteger, adParamReturnValue)

Call cmd.Parameters.Append(prm)

cmd.Execute

Call MsgBox(prm.Value)

conn.Close

Set conn = Nothing

Set cmd = Nothing

ADO raises an error for the constraint violation, but actually the stored procedure handles it. To further confuse an error is not raised if the insert statements are swapped so it is the second that fails instead of the first.

Any ideas?