SQL Server Stored Procedure Dynamic Query not returning a recordset

[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)