ADO Returning Recordset from Temp table in Stored Proc

ADO Returning Recordset from Temp table in Stored Proc

Post by Paul Howel » Tue, 13 Jun 2000 04:00:00



has anyone tried to return a recordset from a temp table inside a stored
procedure?  i alway get an empty recordset back.

thanks
paul

 
 
 

ADO Returning Recordset from Temp table in Stored Proc

Post by nne.. » Wed, 14 Jun 2000 04:00:00


Hi Paul,
Could you give more details.  I have a production code which returns
data from a temp table in a stored procedure (Sybase 11.9.2 and ADO 2.1)

NVN


Quote:> has anyone tried to return a recordset from a temp table inside a
stored
> procedure?  i alway get an empty recordset back.

> thanks
> paul

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

ADO Returning Recordset from Temp table in Stored Proc

Post by Adrian Edmond » Wed, 14 Jun 2000 04:00:00


You'll need to post some code for people to help but my answers is yes, I
have got recordsets back from temp tables.

Adrian


Quote:> has anyone tried to return a recordset from a temp table inside a stored
> procedure?  i alway get an empty recordset back.

> thanks
> paul

 
 
 

ADO Returning Recordset from Temp table in Stored Proc

Post by Paul Howel » Wed, 14 Jun 2000 04:00:00


here is the ADO code to call the stored proc...

        Dim l_adoCmd As ADODB.Command
        Set l_adoCmd = New ADODB.Command

        Dim l_adoRS As ADODB.Recordset
        Set l_adoRS = New ADODB.Recordset

        With l_adoCmd
            .ActiveConnection = p_strConnectionString
            .CommandType = adCmdStoredProc
            .CommandText = "sp_FindDataAndValues"
            .Parameters.Append .CreateParameter("p_intReturnValue",
adInteger, adParamReturnValue)
            .Parameters.Append .CreateParameter("p_strCompanyID", adVarChar,
adParamInput, 20, Mid$(p_strCompanyID, 1, 20))
            .Parameters.Append .CreateParameter("p_strAgentID", adVarChar,
adParamInput, 33, Mid$(p_strAgentID, 1, 33))
            .Parameters.Append .CreateParameter("p_strClientID", adChar,
adParamInput, 33, Mid$(p_strClientID, 1, 33))
            .Parameters.Append .CreateParameter("p_strApplicationID",
adVarChar, adParamInput, 20, Mid$(p_strApplicationID, 1, 20))
            .Parameters.Append .CreateParameter("p_strDataArrayID",
adVarChar, adParamInput, 20, Mid$(p_strDataArrayID, 1, 20))
            .Parameters.Append .CreateParameter("p_strValuesArrayID",
adVarChar, adParamInput, 20, Mid$(p_strValuesArrayID, 1, 20))
        End With

        l_adoRS.Open l_adoCmd, , adOpenStatic, adLockReadOnly

and here is the stored proc...

CREATE PROCEDURE sp_FindDataAndValues (






     )
AS
  set nocount on
  select  array_id, array_text
  into  #temp1
  from  tblARRAY






  select * from #temp1


> Hi Paul,
> Could you give more details.  I have a production code which returns
> data from a temp table in a stored procedure (Sybase 11.9.2 and ADO 2.1)

> NVN


> > has anyone tried to return a recordset from a temp table inside a
> stored
> > procedure?  i alway get an empty recordset back.

> > thanks
> > paul

> Sent via Deja.com http://www.deja.com/
> Before you buy.

 
 
 

ADO Returning Recordset from Temp table in Stored Proc

Post by nne.. » Wed, 14 Jun 2000 04:00:00


Hi Paul,
Did you try executing your stored procedure outside ado, with the
parameters you are passing, to check whether there are any data in the
table.  That seems to be most obvious thing.

Also how do you know that the output is empty.  Which property did you
check? RecordCount, State, EOF, BOF, etc..

hope this helps
NVN



> here is the ADO code to call the stored proc...

>         Dim l_adoCmd As ADODB.Command
>         Set l_adoCmd = New ADODB.Command

>         Dim l_adoRS As ADODB.Recordset
>         Set l_adoRS = New ADODB.Recordset

>         With l_adoCmd
>             .ActiveConnection = p_strConnectionString
>             .CommandType = adCmdStoredProc
>             .CommandText = "sp_FindDataAndValues"
>             .Parameters.Append .CreateParameter("p_intReturnValue",
> adInteger, adParamReturnValue)
>             .Parameters.Append .CreateParameter("p_strCompanyID",
adVarChar,
> adParamInput, 20, Mid$(p_strCompanyID, 1, 20))
>             .Parameters.Append .CreateParameter("p_strAgentID",
adVarChar,
> adParamInput, 33, Mid$(p_strAgentID, 1, 33))
>             .Parameters.Append .CreateParameter("p_strClientID",
adChar,
> adParamInput, 33, Mid$(p_strClientID, 1, 33))
>             .Parameters.Append .CreateParameter("p_strApplicationID",
> adVarChar, adParamInput, 20, Mid$(p_strApplicationID, 1, 20))
>             .Parameters.Append .CreateParameter("p_strDataArrayID",
> adVarChar, adParamInput, 20, Mid$(p_strDataArrayID, 1, 20))
>             .Parameters.Append .CreateParameter("p_strValuesArrayID",
> adVarChar, adParamInput, 20, Mid$(p_strValuesArrayID, 1, 20))
>         End With

>         l_adoRS.Open l_adoCmd, , adOpenStatic, adLockReadOnly

> and here is the stored proc...

> CREATE PROCEDURE sp_FindDataAndValues (






>      )
> AS
>   set nocount on
>   select  array_id, array_text
>   into  #temp1
>   from  tblARRAY






>   select * from #temp1




- Show quoted text -

> > Hi Paul,
> > Could you give more details.  I have a production code which returns
> > data from a temp table in a stored procedure (Sybase 11.9.2 and ADO
2.1)

> > NVN


> > > has anyone tried to return a recordset from a temp table inside a
> > stored
> > > procedure?  i alway get an empty recordset back.

> > > thanks
> > > paul

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.

Sent via Deja.com http://www.deja.com/
Before you buy.