can i write a stored procedure which returns a recordset?

[gash25]

can i write a stored procedure which returns a recordset?
how can this be done? and how do i read it from vb?

[paulw]

Of course you can.

If the stored procedure creates a dataset (i.e. it is not an action query) then you can access the data through ADO.

You would create the ADO Connection as usual and set up an ADO Command. Assume you have a connection 'cn' and a command object 'cmd'. Your stored procedure is called MySPName and it takes two parameters (say). You have saved these parameters in 'prm1' and 'prm2'. You have a recordset, 'rs' to receive the data.

You would then use:

cmd.CommandText = "MySPName"
cmd.CommandType = adCmdStoredProc

Now give it a name

cmd.Name = "MyLocalSP"
cmd.ActiveConnection = cn

When you need the data you would use:

cn.MyLocalSP prm1, prm2, rs

This will pass the parameters to the stored procedure and return the data into a recordset rs (which must be placed after the parameter list).

Note that if the stored procedure has an OUTPUT value or a return status, you would get to these by using ODBCDirect type call.

cmd.CommandText "{?=call MySP (?)}
cmd.CommandType = adCmdText

You would have to build an ADO Parameters collection to access these values. I would start with a simple SP though.

Cheers,

P.