Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

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


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
      @Table_Name VARCHAR(30)
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!



1 Solution
Instead of

SELECT @sql = 'SELECT * FROM ' + @Table_Name


SELECT @sql = 'SELECT * FROM ' + @Table_Name

Another tip, don't use sp_ as your prefix but use usp_

Why Sp_ stored procedures are meant to be system stored procedures , when asked to run a ssp_ stored procedure SQL server searches for it in the master database first
sterlingdevAuthor Commented:
Sorry for the delayed reply but EE continues not to notify me by email when someone answers a question so I found your reply only accidentaly!
Returning to your solution it did not work either... Some other strange things were happening (for example, the use of single-quotes for string variables withing a string (see SELECT @sql = '...') when a solution was working OK in Query Analyzer but in a stored procedure, etc.
In the end what worked was to change the initial solution to:
where '...' is the value which was previously assigned to @sql... I agree it's weird but that's what worked in my case!!!

I'll give you the points though for your prompt reply and for somehow guiding me indirectly to trying different other combinations which finally provided me with the solution...
The big question however remains 'Why are we experiencing different behaviour/response for the same solutions in SQL Server?' and by 'we' I mean people in general...

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now