[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

return table name with query results from stored procedure

Posted on 2005-04-21
5
Medium Priority
?
192 Views
Last Modified: 2010-03-19
I am calling and running the following store procedure from vb.net.

CREATE   PROCEDURE dbo.uspSelectQueries  @msg1 varchar(3000) output
AS
Declare cSteps cursor  for
select New_Sql, table_Name, comment  from tblTotal
declare @SQL VARCHAR(500),  @tblName VARCHAR(300)

open cSteps
FETCH next from cSteps into @Sql, @tblName
while @@fetch_status = 0
begin
     exec(@sql)
    fetch next from cSteps into @sql, @tblName
     
end
close csteps
deallocate csteps
GO

the field New_Sql is a select statement with in my table and is ran with the exec(Qsql) statement which returns the results which I load to different datagrids. The problem I have is the field Table_Name is the name of the results. I want to name the table in the datagrid with this name. So the first query results should also pass Table_Name so I can set the table name in the grid.  The Only thing I can think of doing is storing passing the field Table_Name into a variable and using output parameter. But than I would have to substr or something like that to filter out each table name. Can I use an array within a strored Procedure or is there a way to pass the Table_name with the exec?  Thanks
0
Comment
Question by:MadIce
  • 3
  • 2
5 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 1000 total points
ID: 13835600
open cSteps
FETCH next from cSteps into @Sql, @tblName
while @@fetch_status = 0
begin
    SELECT @tblName AS TableName --- This is what I added
     exec(@sql)
    fetch next from cSteps into @sql, @tblName
     
end
close csteps
deallocate csteps
GO

Try returning the table name as part of the result set.  Then in your VB.NET code, you know that the result sets are table name then data in sequence.
0
 

Author Comment

by:MadIce
ID: 13836290
Well that was cleaner than if I used output parameter and it works. Thanks Again...
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13836356
Glad to hear that it worked.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13844926
Were you able to resolve this issue?
0
 

Author Comment

by:MadIce
ID: 13991743
I accepted this answer before...at least I thought I did.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying 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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

830 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