SQL Server 2005 Stored Procedure Using Table Variable Doesn't Return ResultSet to VB6 App

Question

I have a stored procedure which selects a single field from a single row (based on a conditional statement) and stores it in a local variable. Based on the value of this field I update the same field in the same row of the table (using the same conditional statement). So the procedure first does a select into a local variable and then later updates the same field of the same row. The procedure then returns a result set via a select on a table variable (I have also attempted using a temporary table). The result set does not contain the variable or field I have updated. It doesn't even include any fields from this table.

The procedure works correctly when called from either Management Studio or from a test C# application. However, when called from my VB6 app the result set is not returned. All database updates are still performed however.

I have tried writing the stored procedure with a transaction and without, with TRY...CATCH and without, and both at the same time. I've tried various combinations of transaction isolation. No exceptions are thrown and the transaction will always commit. I've also used the WITH (NOLOCK) hint on the select statement. If I leave out the table update it will work. If I leave out the assignment to a local variable and instead hard code a value it works. If I simply use the select where I would put the variable it will NOT work.

Interestingly, if I add some random select statement to the procedure it will return that result set. I can even select that same field from the same record I assign to my variable with no issue. But it still will not return my desired result set.

My result set is a select from a table variable which is populated via insert statements using variables set throughout the procedure. There are no table joins at all. I do pass 2 parameters to the procedure - one of which is used in my conditional statement in the original select. But I still get the same behavior when I omit both parameters and hard code values.

I have tried restarting my SQL Server (2005 version 9.0.4053), restarting my machine, i have tried with NOCOUNT ON and OFF, I'm basically out of ideas.

Edit - Details of VB call and stored procedure signature:
I'll try to give as good as a description as I can without publishing actual code. I'm actually posting this for another developer who works with me, so please bear with me.

VB6 Call:

 With cmdCommand
        .ActiveConnection = cnnConn
        .CommandType = adCmdStoredProc
        .CommandText = "uspMyStoredProcedure"
        .Parameters("@strParam1") = strFunctionParameter1
        .Parameters("@bolParam2") = bolFunctionParameter2
        .Execute
 End With

MyResultSet.CursorLocation = adUseClient
MyResultSet.Open cmdCommand, , adOpenStatic, adLockReadOnly

Stored Procedure signature:

CREATE PROCEDURE uspMyStoredProcedure
    @strParam1 NVARCHAR(XX),
    @bolParam2 BIT

AS
BEGIN
    SET NO COUNT ON

    DECLARE @var1 NVARCHAR(XX),
    @var2 NVARCHAR(XX),
    @var3 NVARCHAR(XX),
    @var4 INT,
    @var5 BIT
    --DECLARATION OF OTHER VARIABLES


    DECLARE  @varTableVariable TABLE
    (
      strTblVar1 NVARCHAR(XX) ,
      intTblVar2 INT ,
      strTblVar3 NVARCHAR(XX) ,
      bolTblVar4 BIT ,
      datTblVar5 DATETIME
    )

    SELECT @var1 = t.Field1, @var2 = t.Field2
    FROM Table1 t
    WHERE t.ID = @strParam1

    SELECT @var3 = t2.Field1
    FROM Table2 t2

    IF (Condition)
    BEGIN
        SET @var4 = 1
        IF (Condition)
        BEGIN
            --SET SOME VARIABLES
        END
        ELSE
        BEGIN
            UPDATE TABLE1
            SET Field3 = @var4
            WHERE Field1 = @strParam1
        END
    END
    ELSE
    BEGIN
        IF(Condition)
        BEGIN
            SELECT @var5 = ISNULL(Condition)
            FROM Table3 t3
            WHERE t3.Field = @strParam1

            --SET SOME MORE VARIABLES
        END
    END

    IF(Condition)
    BEGIN
        UPDATE Table1
        SET Field5 = @SomeVariable
        WHERE Field1 = @strParam1
    END

    INSERT INTO Table4 (Field1, Field2, Field3)
    SELECT @SomeVar1, @someVar2, @SomeVar3
    FROM SomeOtherTable
    WHERE Field3 = @someVariable

    IF(Condition)
    BEGIN
        INSERT INTO @varTableVariable (strTblVar1, intTblVar2, 
        strTblVar3, bolTblVar4,  datTblVar5 )
        VALUES (@SomeVar1, @SomeVar2, @SomeVar3, @SomeVar4, @SomeVar5)
    END

SELECT *
FROM @varTableVariable

END

So, essentially, the procedure takes two parameters. It carries out a number of simple operations - inserting and selecting data from a couple of different tables, an update to a table and inserting a row into a table variable.

The procedure finishes with a select from the table variable. There's nothing fancy about the procedure, or the call from VB6. As previously stated, the behaviour observed is unusual in that by commenting out certain sections the call and return will work - data is returned. Calling the same procedure from a C#.NET test app works and successfully returns the desired result.

All we manage to get back in the VB6 app is an empty recordset.

Edit 2: We've just found out that if we create an arbitrary table to hold the data to be returned by the final select statement instead of using a table variable, the procedure works...

Answer 1

We discovered that the stored procedure was actually executing twice, due to the way it was being called from VB6:

With cmdCommand
        .ActiveConnection = cnnConn
        .CommandType = adCmdStoredProc
        .CommandText = "uspMyStoredProcedure"
        .Parameters("@strParam1") = strFunctionParameter1
        .Parameters("@bolParam2") = bolFunctionParameter2
        .Execute 
 End With

MyResultSet.CursorLocation = adUseClient
MyResultSet.Open cmdCommand, , adOpenStatic, adLockReadOnly 

The command object 'cmdCommand' is executed the first time with the explicit call as the final line in the 'With' statement, '.Execute'.

What we found was that the last line: 'MyResultSet.Open cmdCommand...' is also implicitly executing the stored procedure a second time.

Since the stored procedure's function is essentially to activate and deactivate an alarm, by executing twice we were getting the activation and deactivation occurring at once and therefore no resultset returned.

Hopefully this might help avoid someone else getting stuck on something like this.