By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Mutiple Table from Stored procedure

P: 4
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.
Jan 28 '09 #1
Share this Question
Share on Google+
6 Replies


ck9663
Expert 2.5K+
P: 2,878
If you are talking about linking multiple tables, you have to find a key that can be use to link those tables and use a JOIN to link them all together.

If you're talking about concatenating those tables, then you just have to match which columns goes to which columns and use UNION ALL.

Happy coding!

-- CK
Jan 28 '09 #2

P: 4
thanks ck9663,

Please have look,

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?
Jan 28 '09 #3

ck9663
Expert 2.5K+
P: 2,878
This would depend on your front-end apps. Try posting this question in the .Net forum for the basics (ie, how to connect to sql server, how to read recordsets, etc). Then we can discuss the T-SQL that will process your data.

Good luck!

-- CK
Jan 28 '09 #4

P: 4
ok,thanks,I will do.

however is it possible to name mutiple record set in store procedure ?

something like -

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

thanks
Jan 28 '09 #5

ck9663
Expert 2.5K+
P: 2,878
No.

Either you create a separate recordset for each table. Or you can use UNION ALL and add a field that can help you identify which table the row came from.

-- CK
Jan 28 '09 #6

P: 4
Thanks ck9663 ,

I check with microsoft as well they says-

"In sort, there's no way to actually "name" your result sets. You're stuck using the 0-based result set ordinal in your client code.
Sorry!
Aaron Alton | [COLOR=#0072bc]thehobt.blogspot.com[/COLOR]"
Jan 29 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.