How to retrieve a recordset from a stored procedure using sp_executes

[datras]

I want to call my stored procedure at the SQL server from VB with a new table name each time and get a recordset back. My stored procedure look something like this:

CREATE procedure upSU_CrossCheck
@tbl nvarchar(10)

as

declare @sqlstring nvarchar(500)
declare @paradef nvarchar(500)
declare @paratbl nvarchar(10)

set @sqlstring = N'
SELECT Country, Ship FROM ' + @tbl


set @paratbl = @tbl
set @paradef = N'@tbl nvarchar(10)'

EXEC sp_executesql @sqlstring, @paradef, @tbl = @paratbl


And my VB:


Dim rst As adodb.Recordset

Set cmd.ActiveConnection = con

cmd.CommandText = "test"
cmd.CommandType = adCmdStoredProc

Set rst = cmd.Execute

Dim n As Integer

If (Not rst.EOF) Then
Do Until rst.EOF
Debug.Print rst(0); rst(1); rst(2)
rst.MoveNext
Loop
End If

However, I do not get a recordset back when I execute it. If I run it from VB it tells me that the recordset is not open. How do I do that?

 

[AndyDuke]

Datras,

The stored procedure probably returned nothing, have you tested the stored procedure outside of VB (with Query Analyser) to ensure that it is working okay.

Andy

 

[datras]

Yes I have and it was returning nothing as well. I have however got the answer in the SQL server forum. The code should be like this:

CREATE procedure upSU_CrossCheck
@tbl nvarchar(10)
AS
declare @sqlstring nvarchar(500)

Set nocount on 'I had forgot this part

SET @sqlstring =
N'SELECT Country, Ship FROM ' + @tbl

EXEC sp_executesql @sqlstring