Dynamic Query Problem

[abdullauthan]

When I try to retrive records through SQL Server Stored Procedure with Dynamic Query, I'm not getting the result as a recordset in VB. But, if I run the Stored Procedure from SQL Server Enterprise Manager, It builts the Dynamic Query very well and returns the result. Any solution to this?

code follows...

In VB:
-------------------

Private cmAction As New ADODB.Command
Private rstAction As New ADODB.Recordset

cmAction.ActiveConnection = cnInv
cmAction.CommandType = adCmdStoredProc
rstAction.CursorLocation = adUseServer
rstAction.CursorType = adOpenKeyset

cmAction.CommandText = "sp_Test"
cmAction.Parameters("@Test&quot.Value = "ID_Junk"
rstAction.Open cmAction
If rstAction.RecordCount > 0 Then
Set grdAction.DataSource = MSHFlexGrid1
End If
rstAction.Close

In SQL Server:
--------------
CREATE PROCEDURE sp_Test @Test VarChar(100) AS
Execute ("Select * From " + @Test)

 

[scottsanpedro]

Hi There,

I use Access and had the same sort of problem. This may be a solution for you.

I build the query in access, as you do in VB, and passed the query across as you are doing. Then wrote the follwing Stored Procedure

CREATE PROCEDURE procAdvancedSearch

@strSQL nvarChar(500) = NULL

AS

SET NOCOUNT ON

DECLARE @SQL nvarChar(500)

SET @SQL = @strSQL

EXEC sp_executesql @SQL

Have a look at
sp_executesql

It may work for you

Scott