How to return table name with columns in select query

Question

In my database there is one stored procedure.In that stored procedure,I am retrieving data from ten different tables.That is in result i am getting 10 tables.My question is,is there any way so I can return table name.Because this SP I am executing from my vb.net project and stored this result in one dataset.And I am merging result of this Stored Procedure,that is tables from this stored procedure to tables in my data set.So i am merging this tables as dataset1.tables("Table1").Merge(dataset2.tables(0))

So now I want to do like,I have to give table name to result from stored procedure.So I can merge table with table name not with table index i.e tables(0).

So should I retrieve such result through stored procedure.

I am having stored procedure like

create proc sp_QM_INVOICE_MATBATCHWISE_DETAILS
    @PLANT_CODE NUMERIC(5,0),
    @COMPANY_CODE NUMERIC(5,0),
    @FYEAR NUMERIC(5,0),
    @STRANSID VARCHAR(15),
    @SMODE VARCHAR(5),
    @DOC_NO VARCHAR(20),
    @INVOICE_NO VARCHAR(20),
    @CUSTOMER_NO VARCHAR(20)
    AS
    BEGIN
    SELECT * FROM QM_INVOICEMATWISE_DETAILS
    WHERE COMPANY_CODE=@COMPANY_CODE 
    AND PLANT_CODE=@PLANT_CODE
    AND FYEAR=@FYEAR
    AND DOC_NO=@DOC_NO
    AND INVOICE_NO=@INVOICE_NO
    AND CUSTOMER_NO=@CUSTOMER

    SELECT * FROM QM_INVOICEMATBATCHWISE_DETAILS
    WHERE COMPANY_CODE=@COMPANY_CODE 
    AND PLANT_CODE=@PLANT_CODE
    AND FYEAR=@FYEAR
    AND DOC_NO=@DOC_NO
    AND INVOICE_NO=@INVOICE_NO
    AND CUSTOMER_NO=@CUSTOMER

    SELECT * FROM QM_TESTCERTIFICATEFORMATCUST_T
    WHERE COMPANY_CODE=@COMPANY_CODE 
    AND PLANT_CODE=@PLANT_CODE
    AND FYEAR=@FYEAR
    AND DOC_NO=@DOC_NO
    AND INVOICE_NO=@INVOICE_NO
    AND CUSTOMER_NO=@CUSTOMER

END

So how i can retrieve this result with table name in result. When I execute this stored procedure through my code,I am retrieving this result in one data set.In data set,tables are like Table1,Table2,Table3.So how I can retrieve the result also but with table name through stored procedure

Hard to understand your question. You want to retrieve a bunch of tables and reference them by name in vb.net, or you are wanting to retrieve just one table with all data merged (in this case with an extra column to know the origin of data)? — Nizam
– Nizam, Commented Jul 27, 2014 at 12:57
I want to retrieve bunch of tables and reference them by name — Ashwini Agivale
– Ashwini Agivale, Commented Jul 27, 2014 at 13:03
Thanks for the answer but I have not understand it properly,can u please explain it — Ashwini Agivale
– Ashwini Agivale, Commented Jul 27, 2014 at 13:13

Nizam · Accepted Answer · 2014-07-27 13:20:08Z

You could union them in SQL Server itself and work with just one table:

create proc sp_QM_INVOICE_MATBATCHWISE_DETAILS
@PLANT_CODE NUMERIC(5,0),
@COMPANY_CODE NUMERIC(5,0),
@FYEAR NUMERIC(5,0),
@STRANSID VARCHAR(15),
@SMODE VARCHAR(5),
@DOC_NO VARCHAR(20),
@INVOICE_NO VARCHAR(20),
@CUSTOMER_NO VARCHAR(20)
AS
BEGIN
SELECT *, 'QM_INVOICEMATWISE_DETAILS' TableName FROM QM_INVOICEMATWISE_DETAILS
WHERE COMPANY_CODE=@COMPANY_CODE 
AND PLANT_CODE=@PLANT_CODE
AND FYEAR=@FYEAR
AND DOC_NO=@DOC_NO
AND INVOICE_NO=@INVOICE_NO
AND CUSTOMER_NO=@CUSTOMER

SELECT *, 'QM_INVOICEMATBATCHWISE_DETAILS' TableName FROM QM_INVOICEMATBATCHWISE_DETAILS
WHERE COMPANY_CODE=@COMPANY_CODE 
AND PLANT_CODE=@PLANT_CODE
AND FYEAR=@FYEAR
AND DOC_NO=@DOC_NO
AND INVOICE_NO=@INVOICE_NO
AND CUSTOMER_NO=@CUSTOMER

SELECT *, 'QM_TESTCERTIFICATEFORMATCUST_T' TableName  FROM QM_TESTCERTIFICATEFORMATCUST_T
WHERE COMPANY_CODE=@COMPANY_CODE 
AND PLANT_CODE=@PLANT_CODE
AND FYEAR=@FYEAR
AND DOC_NO=@DOC_NO
AND INVOICE_NO=@INVOICE_NO
AND CUSTOMER_NO=@CUSTOMER

UPDATE

As you want to reference a bunch o tables by name, you need to read first row of each table (field TableName) and set the property TableName of each datatable.

Right after call the procedure, you should run:

for each tbl in dataset1.tables 
   if tbl.Rows.Count > 0
      tbl.TableName = tbl.Rows(0)("TableName")
   end if
next

The you will be able to reference them as dataset1.tables("QM_TESTCERTIFICATEFORMATCUST_T"), for example.

As you know the order you are referencing them in Stored Procedure you could hard code the name of tables in vb.net and avoid the issues that would happen in case of empties tables.

mehdi lotfi · Accepted Answer · 2014-07-27 13:16:04Z

0

you can use Extended property for your stored procedure in order to define your resultset metadata of your stored procedure and get this information by using following query:

SELECT ep.name, ep.value
FROM sys.procedures p
INNER JOIN sys.extended_properties ep ON p.object_id = ep.major_id
WHERE p.name = 'YourProcedure'

In this method you can save each meta data for stored procedure like tables name , ordering and column friendly name as extended property.

answered Jul 27, 2014 at 13:16

mehdi lotfi

11.6k18 gold badges85 silver badges128 bronze badges

Collectives™ on Stack Overflow

How to return table name with columns in select query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related