Gettng ADO recordset from nested stored procedures problem

From: Mario Splivalo (majk_at_fly.srk.fer.hr)
Date: 11/12/04 

Date: Fri, 12 Nov 2004 01:53:40 +0000 (UTC)

I have a stored procedure wich takes the document type as input parametar
and returns newly generated document number as OUTPUT parametar. So, if I
wanted new invoice number, i'd say:

exec ep_CreateNewNum 'INV', @docId OUTPUT

and @docID would have 'INV1ZG1200400141', for instance.

Now, since the stored procedure returns data trough OUTPUT parametar, I
can't just do a .Execute on ADODB.Connection to populate ADODB.Recordset.
Since I wanted to avoid all the 'trouble' with ADODB.Commands, and I have
several 'adoexec' wrapper functions (I'm actually using FoxPro6), I created
another stored procedure wich calls the ep_CreateNewNum, and then returns
the data as a recordset, by SELECTing it. Here is the procedure:

CREATE procedure ep_zemag_NewNum
        @docType char(10)
as

declare @newNum char(16)
declare @retvalue int
declare @retNum char(16)

begin transaction
save transaction trn_zemag_NewNum

exec @retValue = ep_CreateNewNum @docType, @newNum OUTPUT

if (@retValue = 0)
begin
        commit transaction trn_zemag_NewNum
        set @retNum=@newNum
end
    else
begin
        rollback transaction trn_zemag_NewNum
        commit transaction
end

select @retNum as doc_id

go

Now, when I run 'ep_zemag_NewNum' troug the Query Analyzer, everything works
perfectly fine, it returns a single row single column recordset with the
newly generated number.

BUT! When I try to call the stored procedure trough ADO using Visual Basic,
I get this error: 'item cannot be found in the collection corresponding to
the requested name or ordinal (3265)'. Here is the VB code:

    Dim rs As ADODB.Recordset
    Dim strSQL As String
    strSQL = "exec ep_zemag_newnum 'TMPDOC'"
    Set rs = SQLExecute(strSQL)
    Debug.Print rs(0)

SQLExecute is a wrapper function wich returns creates the connection, gets
data, and returns ADODB.Recordset.

So, it breaks on 'Debug.print rs(0)', when I try to 'read' rs(0). When I
change the SQL command, eg: "SELECT 'a horse'", it works fine.

And now, the fun part:

If I replace, in the above stated stored procedure, the last 'select'
clause to:

select 'I am a horse' as doc_id

and I comment out the 'exec @retvalue =...', Visual Basic in the Debug
window displays 'I am a horse', as expected. BUT! If I remove earlierly
placed comment on 'exec @retvalue=...' (the select remains the same), I'm
blessed with the 'item cannot be found in the collection....'.

I presume that the 'exec...' using OUTPUT parametar confuses ADO somehow,
but I'm not sure why, nor how.

I understand that this particular example should be dealt with ADO Command
objects on the client side, without the 'ep_zemag_NewNum', but, what If
I have a stored procedure wich calls dozens of stored procedures with return
data to originating procedure via OUTPUT parametars, and that procedure
returns a recordset, based on those OUTPUTs? Or, whenever I have OUTPUT
clause in the stored procedure code (one should note that ep_zemag_NewNum
doesn't return ANY data using OUTPUT parametars), I need to use ADO Command?

Or I have completely wrong ideas about how ADO Command and Recordset objetcs
work?

Any help here would be much appreciated.

        Mario

P.S. I neglected to mention that I'm using MSSQL2000 sp3a and MS VIsual
Studio 5 SP5 (actual application is in Fox, but I used Visual Basic for
testing for sake of convinience). 

-- 
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@jagor.srce.hr

Relevant Pages

  • Gettng ADO recordset from nested stored procedures problem
    ... I have a stored procedure wich takes the document type as input parametar ... Now, since the stored procedure returns data trough OUTPUT parametar, I ... I understand that this particular example should be dealt with ADO Command ...
    (microsoft.public.sqlserver.programming)
  • Gettng ADO recordset from nested stored procedures problem
    ... I have a stored procedure wich takes the document type as input parametar ... Now, since the stored procedure returns data trough OUTPUT parametar, I ... I understand that this particular example should be dealt with ADO Command ...
    (microsoft.public.data.ado)
  • RE: [MSSQL2000] Stored procedures behaving weird?
    ... as recordset and not output param. ... > Now, since the stored procedure returns data trough OUTPUT parametar, I ... > doesn't return ANY data using OUTPUT parametars), I need to use ADO Command? ...
    (microsoft.public.sqlserver.programming)
  • RE: [MSSQL2000] Stored procedures behaving weird?
    ... as recordset and not output param. ... > Now, since the stored procedure returns data trough OUTPUT parametar, I ... > doesn't return ANY data using OUTPUT parametars), I need to use ADO Command? ...
    (microsoft.public.vb.database)
  • Re: Return value in stored procedure
    ... In Delphi (you can use TADOStoredProc, TADOQuery, but I would use ... (selecting the stored procedure of choice) ... select the Parameters property and verify Delphi 'filled in' ... begin {Start of with ADO command do} ...
    (borland.public.delphi.database.ado)