Mutiple Tables(record sets) from Stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mutiple Tables(record sets) from Stored procedure

Author

Topic

dew3it
Starting Member

14 Posts

Posted - 2009-01-28 : 05:10:13

Hi

I am using windows applicaiton(.net) as front end,
and I want to get mutiple tables in Dataset by executing a single stored procedure i.e.
SP would return mutiple tables (record sets) with the user specified name.

can anybody guide me no this?

thanks.

dev

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 05:15:07

in a sp u can write a multiple select statements in it

dew3it
Starting Member

14 Posts

Posted - 2009-01-28 : 05:17:53

yep,thats right,but I am bit of confused on how can I set the name of those mutiple select statements.

dev

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 05:20:14

no need to set the name
select col1,col2,......... from table1

select t.col1,s.col2,.... from table2 s inner join table1 t on t.col3= s.col1

then i will return the 2 datasets

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-28 : 05:41:29

Hi,

we cannot name those mutiple select statements in a storedprocedure. you have to use the index of data tables.

dew3it
Starting Member

14 Posts

Posted - 2009-01-28 : 05:46:22

but in this case how can I distinguish
which record set contains which data.

I don't want to use index[0],[1] in code for refering table bcz
its more open to mistake.

dev

dew3it
Starting Member

14 Posts

Posted - 2009-01-28 : 06:21:27

Is it not possible to do it without using the index of data tables?

dev

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 06:24:25

then post this data in .net forum about front end indexes

dew3it
Starting Member

14 Posts

Posted - 2009-01-28 : 08:23:16

index in dataset I know very well,
my problem is from sql side ,I wanted to send the name as well with all record sets return from stored procedure.

dev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 08:41:24

name as well as recordsets? what does that mean? can you illustrate with some sample data?

dew3it
Starting Member

14 Posts

Posted - 2009-01-28 : 09:36:11

create proc mutiDataSet
As
Begin
select * from temp1

select * from temp2

End

I need to create one procedure in above way but when it execute it returns mutiple record set to front end application e.g. .net dataset
those tables can be accessed in following way.
ds.table[0].
ds.table[1].

but inspite of putting 0,1.... I want get some table name form SP

somthing like -

create proc mutiDataSet
As
Begin
(select * from temp1) as EmpData

(select * from temp2) as DeptData

End

so that I can access in below way

ds.table[EmpData].
ds.table[DeptData].

how it can be possible?

thanks

dev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 09:38:51

do you mean this?


create proc mutiDataSet
As
Begin 
select *,'EmpData' as Cat  from temp1
union all
select *,'DeptData' from temp2 

End

and then filter by cat at front end?

dew3it
Starting Member

14 Posts

Posted - 2009-01-28 : 10:15:43

union All - is not required

each select should return a separate recordset (separate table in front end)

create proc mutiDataSet
As
Begin
(select *,'EmpData' from temp1) as Cat

(select *,'DeptData' from temp2) as Cat1

End

I am not sure if this or something like this is possible,
I need to test.

dev

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-28 : 12:58:16

nope union all will bring them as a single resultset and you can then split it at your front end using last column value (cat)

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 23:11:14

quote:
Originally posted by dew3it

create proc mutiDataSet
As
Begin
select * from temp1

select * from temp2

End

I need to create one procedure in above way but when it execute it returns mutiple record set to front end application e.g. .net dataset
those tables can be accessed in following way.
ds.table[0].
ds.table[1].

but inspite of putting 0,1.... I want get some table name form SP

somthing like -

create proc mutiDataSet
As
Begin
(select * from temp1) ~~as EmpData~~
(select * from temp2) ~~as DeptData~~

End

so that I can access in below way

ds.table[EmpData].
ds.table[DeptData].

how it can be possible?

thanks

dev

u can't give the alias for single select statements
u can differ the select statements by seeing the columns only

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources