Multiple Queries in one Stored procedure

sudevsu

Hello everyone... I have five tables say T1,T2,T3,T4,T5. Each table has different columns say like T1 has T1CL1,T1CL2,T1CL3 and Table T2 has T2CL1,T2CL2,T2CL3 and so on... Now I want to Create a Stored procedure selecting only few columns from Each table and it should return everything in only one Dataset. Is it possible? If Yes... This is what I tried and didn't get anything I want.

Select T1CL1,T1CL2 from T1 where T1CL3 = 'Something'
Select T2CL1,T2CL3 from T2 where T1CL2 = 'prm1'
Select T3CL3,T3CL4 from T3 where T3CL2 = 'prm2'
Select T4CL1,T4CL3 from T4 where T4CL2 = 'prm3'
Select T5CL1,T5CL3 from T5 where T1CL2 = 'prm4'

But I don't know how save the data from each query and place everything from SP into a Dataset? Can anyone help me with this Please

Your help is much appreciated. Thanks Happy Coding!

PIEBALDconsult

Perhaps you want a UNION?

sudevsu

Correct something like that. I tried few in google but didn't find anything helpful. So I want to ask experts in here to find a solution.

Your help is much appreciated. Thanks Happy Coding!

sudevsu

I also read something below

the number of columns appears in the corresponding SELECT statements must be equal.
The columns appear in the corresponding positions of each SELECT statement must have the same data type or at least convertible data type.

I don't have same number of columns that must be selected. Neither of same datatype

Your help is much appreciated. Thanks Happy Coding!

Corporal Agarn

Put in nulls for the tables that do not have enough columns. Change the data type by using CAST or CONVERT on the column within the select statement.

Mongo: Mongo only pawn... in game of life.

Wendelius

Normally you would use union or union all. So in this case

Select T1CL1,T1CL2 from T1 where T1CL3 = 'Something'
union all
Select T2CL1,T2CL3 from T2 where T1CL2 = 'prm1'
union all
Select T3CL3,T3CL4 from T3 where T3CL2 = 'prm2'
union all
Select T4CL1,T4CL3 from T4 where T4CL2 = 'prm3'
union all
Select T5CL1,T5CL3 from T5 where T1CL2 = 'prm4'

However you said that you actually have different amount of columns and the data types are do not match or cannot be converted. If that really is the case, then you need to fetch multiple result sets from a single procedure. Also if you can execute the statement as a batch from client side, then have a read at Executing multiple SQL statements as one against SQL Server[^]

sudevsu

Can I use Join if there is a Table T6 which has all the primary keys of T1,T2,T3,T4 and T5?

Your help is much appreciated. Thanks Happy Coding!

Richard Deeming

You don't need to enable MARS to fetch multiple result-sets from a single query; you just load each result-set sequentially. A SqlDataAdapter will take care of that for you, or you can call IDataReader.NextResult in a loop, loading each result-set as you go. You only need MARS if you're going to have multiple active data-readers open on the same connection at the same time. In other words:

// This needs MARS:
using (var connection = new SqlConnection("..."))
using (var outerCommand = new SqlCommand("...", connection))
{
connection.Open();
using (IDataReader outerReader = outerCommand.ExecuteReader())
{
while (outerReader.Read())
{
using (var innerCommand = new SqlCommand("...", connection))
{
using (IDataReader innerReader = innerCommand.ExecuteReader())
{
while (innerReader.Read())
{
...
}
}
}
}
}
}

// This doesn't:
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
connection.Open();
using (IDataReader reader = command.ExecuteReader())
{
do
{
while (reader.Read())
{
...
}
}
while (reader.NextResult())
}
}

// Neither does this:
var dataSet = new DataSet();

using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
var adapter = new SqlDataAdapter(command);
adapter.Fill(dataSet);
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

sudevsu

Thank you Rich

Your help is much appreciated. Thanks Happy Coding!

Wendelius

You can use join in a normal way on each select

Wendelius

That's true. Don't know what I was thinking... Thanks for pointing that out.

Smart003

Hello, i had done this in oracle. see what i had did. created the tables

create table tqw1(t1c1 varchar2(10),t1c2 varchar2(10),t1c3 varchar2(10),t1c4 varchar2(10));
create table tqw2(t2c1 varchar2(10),t2c2 varchar2(10),t2c3 varchar2(10),t2c4 varchar2(10));
create table tqw3(t3c1 varchar2(10),t3c2 varchar2(10),t3c3 varchar2(10),t3c4 varchar2(10));
create table tqw4(t4c1 varchar2(10),t4c2 varchar2(10),t4c3 varchar2(10),t4c4 varchar2(10));

insert some random data into those tables

insert into tqw1(t1c1,t1c2,t1c3,t1c4) values (7,2,3,4);
insert into tqw2(t2c1,t2c2,t2c3,t2c4) values (8,3,4,5);
insert into tqw3(t3c1,t3c2,t3c3,t3c4) values (9,4,5,6);
insert into tqw4(t4c1,t4c2,t4c3,t4c4) values (10,5,6,7);

see there was no relation among these tables. so i had used alias with the tables to select a particular data set

select Q.T1C1,W.T2C2,E.T3C3,R.T4C4 from tqw1 q,TQW2 w,TQW3 e, TQW4 r ;

need the use where clause to filter the data

Abdulnazark

add one column of to identify the table name like Select 'T1' as tbl, T1CL1 as col1,T1CL2 as col2 from T1 where T1CL3 = 'Something' union all Select 'T2', T2CL1,T2CL3 from T2 where T1CL2 = 'prm1' union all Select 'T3', T3CL3,T3CL4 from T3 where T3CL2 = 'prm2' union all Select 'T4',T4CL1,T4CL3 from T4 where T4CL2 = 'prm3' union all Select 'T5',T5CL1,T5CL3 from T5 where T1CL2 = 'prm4'

deepankarbhatnagar

Try to use cross join with the tables & select recpective column which you require.

hi