Stored Procedure

[WadeD]

Hi, how can I read/return a recordset from a stored procedure in SQL Server 7? For SQL, I normally use something like:

cmd.CommandText = "SELECT Name, Addr FROM Employee WHERE Name Like 'A%'
Set rstLocal = cmd.Execute(, , vbReadOnly)

So for a Stored Procedure that I created, I tried:

cmd.CommandText = "EXEC km_TEST 'dimaccow'"  'This works from Query Analyzer but not VB
Set rstLocal = cmd.Execute(, , vbReadOnly)

km_Test is the Stored Proc and I'm passing a name. Doesn't work from VB for me. What am I doing wrong?

Thanks in advance,
Wade

[LG]

Hi, there.

Check this sample:

http://support.microsoft.com/support...&SA=ALLKB&FR=0

Larisa

[Gen-X]

What you are forgetting is that stored procedures are different to just running a "SELECT" statement.

When you run a SELECT statement in VB you are actually creating what is called a cursor. Internally it sets up 3 procedures for you.

- Open Cursor
- Fetch
- Close Cursor

Of course there is some code in there to determine between "MoveFirst", "MoveNext" and "MovePrev" to alter which record the "Fetch" will bring back for you...

Stored Procedures on the other hand start AND finish with their calling.... they don't return in the same way because they are encapsulated inside the procedure.

I know there are ways of making cursors but they are not very easy....

I would have to ask "What possible reason could you have for putting a select statement into a stored procedure?"