Hi Friends .. need a small help can anyone help me in showing how to call a stored procedure which returns a recordset .. yea i need to pass one parameter also for getting that required data .. thanks in advance..


Sameer

Just use a recordset object as you normally would. i.e. say your SP has a name of MySP and it accepts one parameter. So you query string would be something like...
Then execute the query as you normally would.

Good Luck

Hi Sameer,

If you are using VB6 then u need to use Command object.

Please see the below example
Dim mobjConn As ADODB.Connection
Dim mobjCmd As ADODB.Command
Dim mobjRst As ADODB.Recordset

Set mobjCmd = New ADODB.Command

mobjCmd.CommandType = adCmdStoredProc

mobjCmd.Parameters.Append mobjCmd.CreateParameter("<Param Name> ", <Param Datatype>, <Param Type>, ,<Value>)

mobjCmd.CommandText = strSPName
mobjCmd.Execute

Please let me know if you have any issues

Mahesh B Sayani

Correct me if I am wrong maheshsayani or anyone else, but using a command object used that way will not return a recorset....

>need a small help can anyone help me in showing how to call a stored procedure which returns a recordset

Hence, my suggestion to use a recordset object...

I am sure you know that the result of command.execute is record set. so may be explain your question more?

As I did not understand the PO question, I did not on which back end he talking about. does he mean a SP in SQL Server or a normal function in vb he is calling it stored procedure.

yup please specify waht db you are using and what connection you use, is it either DAO or ADO.

Yes, I do realize that if you...
but the way maheshsayani used it...
does not return a recordset, hence my post...

As for understanding the OP's origional Q see post #1 above. It seems pretty strait forward to me...

Good Luck

'ADO is a DataEnvironment
'conGeneral is a DEConnection (to connect to the SQL Server)

'on the form dim the recordset

Dim rsRecord As New ADODB.Recordset

'then call the Stored Procedure "pro_StoredProc" with the parameter
With rsRecord
If .state = adStateOpen Then .Close
'
' set propertie of recordset (don't need if only read records)
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
' open recordset with a numeric Parameter
.Open ("{Call pro_StoredProc(" & Parameter & ")}"), ADO.conGeneral
' open recordset with a String Parameter
.Open ("{Call pro_StoredProc('" & Parameter & "')}"), ADO.conGeneral
'then go on as usual
Do Until .EOF
'read the record
.MoveNext
Loop

End With