Passing Recordset to sp

Passing Recordset to sp

Post by Doo » Fri, 08 Dec 2000 04:00:00



I don't see how that can happen.

You could, however, have the SP create the record set you intended to pass
it.

--
Doo
Senior Data Architect / DBA
PlanetJam Media Group


I want to send a recordset to a Stored Procedure through ADO and have the
Stored
Procedure take the recordset data and append it to a table, matching field
names.  How can I do the Stored Procedure part of that?

(SQL Server 7.0, Access 2K)

 
 
 

Passing Recordset to sp

Post by Bill Woodcoc » Fri, 08 Dec 2000 04:00:00


I have seen someone at least pass an array of data to a sp and use data from that
to process info.  Is that a feasable solution instead of a recordset?  If so, any
ideas on how that is done?
-----Original Message-----

You can't, a potential solution to this would be to use a temporary table,
populate that in your application and then reference it in the stored
procedure.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK SQL Server User Group]



I want to send a recordset to a Stored Procedure through ADO and have the
Stored
Procedure take the recordset data and append it to a table, matching field
names.  How can I do the Stored Procedure part of that?

(SQL Server 7.0, Access 2K)

.


 
 
 

Passing Recordset to sp

Post by Don Arsenaul » Fri, 08 Dec 2000 04:00:00


You also can't pass an array to a stored procedure; there's no such entity
in SQL Server.

If there's a lot of data, a temp or working table is probably best.  Your
client uses ADO methods to create the table (if temp), fill the table,
execute the stored procedure, then drop the table.

If there isn't much data, you can use optional parameters to your benefit.
    create procedure a(




    as



        ...

If the processing is a little more complicated than a simple insert, do
something like this instead.
    create procedure a(




    as
        declare cntr int


        select cntr=0

            begin






                ....
                end





                ....
                end


            end

You can also concatenate your columns and rows into one or more varchar
procedure parameters.  Fixed width layout of the columns and rows is easiest
to parse; delimitted columns and rows are harder to parse in a stored
procedure.  SQL7 accepts up to 8000 characters.  Use WHILE to loop.
SUBSTRING() to get individual column values (parse).  RTRIM() to trim off
padding characters.

    as





            begin




            end

Don.


I have seen someone at least pass an array of data to a sp and use data from
that
to process info.  Is that a feasable solution instead of a recordset?  If
so, any
ideas on how that is done?

 
 
 

Passing Recordset to sp

Post by Bill Woodcoc » Sat, 09 Dec 2000 02:18:45


I want to send a recordset to a Stored Procedure through ADO and have the Stored
Procedure take the recordset data and append it to a table, matching field
names.  How can I do the Stored Procedure part of that?

(SQL Server 7.0, Access 2K)

 
 
 

Passing Recordset to sp

Post by Tony Rogerso » Sat, 09 Dec 2000 02:58:27


You can't, a potential solution to this would be to use a temporary table,
populate that in your application and then reference it in the stored
procedure.

--
Tony Rogerson SQL Server MVP
Torver Computer Consultants Ltd
www.sql-server.co.uk [UK SQL Server User Group]


I want to send a recordset to a Stored Procedure through ADO and have the
Stored
Procedure take the recordset data and append it to a table, matching field
names.  How can I do the Stored Procedure part of that?

(SQL Server 7.0, Access 2K)

 
 
 

Passing Recordset to sp

Post by jf » Fri, 09 Mar 2001 03:04:54


Quote:> If there's a lot of data, a temp or working table is probably best.  Your
> client uses ADO methods to create the table (if temp), fill the table,
> execute the stored procedure, then drop the table.

how do i use temp table?
for example i have a recordset, which has different records each time.
right now i loop the recordset to insert a record one time.

in my case, how do i use temp table and stored procedure to insert the
recordset?