OUTPUT Params and RecordSet Problem



I'm trying to call a procedure that has OUTPUT params and returns a record
set. In Excel 2003 VBA (Microsoft ActiveX Data Objects 2.8 Library), I seem
to only be able to get one or the other.

In SQL Server 2005, I got the expected results running this script:

USE ICEBOX;
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

IF EXISTS
(SELECT * FROM INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_TYPE = 'PROCEDURE'
AND ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME = 'USP_ProcedureTest'
)
DROP PROCEDURE dbo.USP_ProcedureTest;
GO

CREATE PROCEDURE dbo.USP_ProcedureTest
(
@String256 VARCHAR(256)
,@ReverseString256 VARCHAR(256) OUTPUT
,@RStringLength INT OUTPUT
) AS SET NOCOUNT ON;
BEGIN
SET @ReverseString256 = REVERSE(@String256);
SET @RStringLength = LEN(@ReverseString256);

SELECT '@String256' AS Variable, @String256 AS Value
UNION ALL
SELECT '@ReverseString256' AS Variable, @ReverseString256 AS Value
UNION ALL
SELECT 'RETURNS' AS Variable, CAST(@RStringLength AS VARCHAR) AS Value;
END;
GO

DECLARE @Return INT, @Input VARCHAR(256), @OutPut VARCHAR(256), @Len INT;
SET @Input = 'StringToReverse';
EXEC @Return = dbo.USP_ProcedureTest
@String256 = @Input
,@ReverseString256 = @Output OUTPUT
,@RStringLength = @Len OUTPUT;
PRINT '@Return' + CHAR(9) + CAST(@Return AS VARCHAR);
PRINT '@Input' + CHAR(9) + @Input;
PRINT '@Output' + CHAR(9) + @Output;
PRINT '@Len' + CHAR(9) + CAST(@Len AS VARCHAR);
GO

In Excel 2003 VBA, in the following function, I have this section:


With ADODB_Command
.ActiveConnection = ADODB_Connection
.CommandType = adCmdStoredProc
.CommandText = "dbo.USP_ProcedureTest"
.Parameters.Refresh
.Parameters("@String256").Value = "StringToReverse"
.Execute Options:=adExecuteNoRecords
strReverse = .Parameters("@ReverseString256").Value
lngLength = .Parameters("@RStringLength").Value
Set ADODB_RecordSet = .Execute()
End With

Notice I have .Execute in there twice. I did that because it seems I have to
use

..Execute Options:=adExecuteNoRecords

to get the OUTPUT parameters to not be NULL, and I use

Set ADODB_RecordSet = .Execute()

To get the record set back. It seems to me I should be able to do both tasks
without executing the statement twice.

Any Ideas?

Thanks, Bob

Here is the full Function

Option Explicit
'Include Reference to Microsoft ActiveX Data Objects 2.8 Library

Private Function ExecuteTestProcedure(strConnection As String, Optional
blnIncludeFieldNames As Boolean = True, Optional strError As String = "") As
Variant
Dim