Stored Procedure Question

[Hum Dum]

Create Procedure MultiTable
As
Begin
Select Name, Email from A
Select X,Y from B
Select J, K from C
End

When We call this Sp return result set in tables as table, table1, table2. Can we give these table name what we want? I mean instead of table, table1 it should give A,B..

SqlConnection conn = new SqlConnection("Con String");
SqlDataAdapter da = new SqlDataAdapter("MultiTable",conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();
da.Fill(ds);

[Lost User]

You cannot decide name of the resultset inside the SP. The ADO.NET will give the default name, like first resultset has name "Table", second has "Table1", and so on. HTH

Jinal Desai - LIVE Experience is mother of sage....

[J4amieC]

Folloowing on from the above answer, ADO.NET assigns the first resultset the name "Table", the second "Table1", the third "Table2" and so on. Therefore you can map these table names to names of your choosing:

da.TableMappings.Add("Table","A");
da.TableMappings.Add("Table1","B");
da.TableMappings.Add("Table2","C");

[ddecoy]

You could also add the tablenames as out parameters in the stored procedure.

@Table Text OUTPUT
,@Table1 Text OUTPUT
AS

BEGIN -- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
SET @Table = 'FirstTableName'
SET @Table1 = 'SecondTableName'
-- Insert statements for procedure here
SELECT * FROM A
SELECT * FROM B

END

And in code you just match the tablenames:

foreach (SqlParameter p in dataAdapter1.SelectCommand.Parameters)
{
dataSet1.Tables[p.ParameterName].TableName = (string) p.Value;
}

[J4amieC]

X| You can also bang in a nail with a pair of pliers - doesn't mean its a good idea!

[ddecoy]

If you only got pliers, than it's a good idea...

[Dave Kreskowiak]

Only if you, or the person who has to maintain your code, don't need to use the pliers later...

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
     2006, 2007, 2008
But no longer in 2009...