?
Solved

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

Posted on 2005-03-16
2
Medium Priority
?
188 Views
Last Modified: 2010-04-23
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


0
Comment
Question by:sterlingdev
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 1000 total points
ID: 13554958
Instead of

SELECT @sql = 'SELECT * FROM ' + @Table_Name
RETURN EXEC(@sql)
GO

do

SELECT @sql = 'SELECT * FROM ' + @Table_Name
EXEC(@sql)
RETURN


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
0
 

Author Comment

by:sterlingdev
ID: 13659970
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:
EXEC('...')
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...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question