How to retrieve a recordset from a stored procedure using sp_executes 1

[datras]

I want to call my stored procedure 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

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?

 

[tlbroadbent]

Add the following line at the beginning of the stored procedure.

Set nocount on

I think you've made the procedure far more complex than it should be. The following should return the result you want.

CREATE procedure upSU_CrossCheck
@tbl nvarchar(10)
AS

Set nocount on

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

EXEC sp_executesql @sqlstring Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[datras]

Hi Terry
Thanks a lot! You are right it was very complicated and now it looks much more logic (-: