RE: [MSSQL2000] Stored procedures behaving weird?
From: Mal .mullerjannie_at_hotmail.com> (.mullerjannie_at_hotmail.com)
Date: 11/12/04
- Next message: Jon Jahren: "Re: Whats the benefit of having different instances of SQL Server"
- Previous message: Michael Cheng [MSFT]: "Re: Cartesian product query"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 12 Nov 2004 02:42:07 -0800
I don't have a solution although I could tell you what I would try.
As you did specify , the output param might not be passing the ouput into
your recordset.
Why not try to use a select statement in the procedure to return the value
as recordset and not output param. I would also try and use a function if the
first approach fail.
Hope it help.
Mal
"Mario Splivalo" wrote:
> 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
>
- Next message: Jon Jahren: "Re: Whats the benefit of having different instances of SQL Server"
- Previous message: Michael Cheng [MSFT]: "Re: Cartesian product query"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
