Calling an SQL Server 2000 stored procedure passing it the table name as an argument and returning data

Hi,

I need to call an SQL Server 2000 stored procedure from a VB.NET application, pass it the table name to be used as an argument and return data back to the VB.NET calling application.

I've simplified this example to make it easier...

Thus, the VB.NET code is as follows:
        Dim cn As New SqlConnection
        Dim cmd As SqlCommand
        Dim SQLReader As SqlDataReader
        Const sp = "sp_MyStoredProcedure"    

           ' Open connection
            cn.ConnectionString = Me.TPSSqlConnectionString     ' Note: Correctly returned from a different routine!
            cn.Open()

            ' Set up command object for stored procedure
            cmd = New SqlCommand(sp, cn)
            cmd.CommandType = CommandType.StoredProcedure

            ' Clear parameter's collection
            cmd.Parameters.Clear()

            ' Add stored procedure's parameters for the TPS_Headers table
            cmd.Parameters.Add(New SqlParameter("@Table_Name", SqlDbType.VarChar))
            cmd.Parameters.Item("@Table_Name").Value = TableName          ' Note: TableName holds the name of the table as a String variable

            ' Run stored procedure and return data
            SQLReader = cmd.ExecuteReader()

If the stored procedure is coded like this:
CREATE PROCEDURE sp_MyStoredProcedure
      @Table_Name VARCHAR(30)
AS
DECLARE @sql varchar(100)
SELECT @sql = 'SELECT * FROM ' + @Table_Name
RETURN EXEC(@sql)
GO

... the application does not fall over on the SQLReader = cmd.ExecuteReader() statement, but the SQL Data Reader variable SQLReader is empty (i.e. SQLReader.HasRows is FALSE) while there is data present on the table. (Then, the SQL Server help pages state that an EXECUTE statement cannot return any data)

So, I've dropped RETURN and used only EXEC(@sql) in my stored procedure. In this case the VB.NET application falls over on the SQLReader = cmd.ExecuteReader() statement.

In conclusion, according to the information I've found, to pass the table name as a variable to the stored procedure, one must build a string which holds the SQLstatement, then run it using an EXEC statement. However, an EXEC statement cannot return any data...

What can I do?
A quick answer to solve this problem exactly in the circumstances described above will be much appreciated!

Thanks!

sterlingdev