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!
' Set up command object for stored procedure
cmd = New SqlCommand(sp, cn)
cmd.CommandType = CommandType.StoredProcedure
' Clear parameter's collection
' 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
DECLARE @sql varchar(100)
SELECT @sql = 'SELECT * FROM ' + @Table_Name
... 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!