SP return multiple records for a single record

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

General SQL Server Forums

New to SQL Server Programming

SP return multiple records for a single record

Author

Topic

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-03 : 17:23:46

I have two tables
TermID, Term
1--- Abc
2--- Test
4--- Tunic

and
TermID, RelatedTermID
1 --- 2
1--- 4
2--- 4

I need to get back something like this

TermID, Term, RelatedTermsInformation
1--- test--- test,tunic#1,4

that above was my solution, get the relatedterms information and comma separate, and then put a # and get all the ids comma separate them and then put the in one field. then I can later parse it in the client

this does not seem like a very good solution ( or is it?)
If posible it would be nice to get something like this

TermID, Term, RelatedTermsInformation
1 test RelatedTermsTwoDimentionalArray

but I am not sure how this idea could be implemented using the capabilities of SQL.

my other option is have the client make one call to the database to get the terms and then lots of another calls to get the relatedTerms, but that will mean one trip to the DB for the list term, and one call for every single term found.

any ideas in how to make this better ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 18:19:32

join them together, get all in ine call and parse the thing at the client. it's what it's there for.

if you're working with ado.net you can just return 2 recordsets and have a master child relationship by default.
just have your stored procedure return 2 selects.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-03 : 18:30:31

so far I have call a stored procedure but only expecting one record set, I am not sure how to handle two, could you point me to some sample online ?. I tried googling but i am not sure I understand it correcly

this is how I have been calling my SP so far

public DataView GetRelatedTerm_SeeAlso(int TermID)
{
try
{
DB data = new DB
List<DbParameter> paramlist = new List<DbParameter>();
paramlist.Add(data.CreateParameter("@TermID", TermID));
return data.FetchDataSet("dbo.Glossary_Fetch_Terms_AND_RELATED_TERMS", ref paramlist).Tables[0].DefaultView;
}
catch
{
throw;
}
}

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 18:38:39

here's the link with an example:
http://shan-tech.blogspot.com/2005/08/sql-server-stored-procedures-returning.html

DB data = new DB ?
Is this enterprise library?
because if it is... can't help you there. haven't touched the thing yet.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-03 : 18:42:06

ya that is enterprise library .. and i am just starting to touch it .... it is a rought ride... it is supposed to make things better but until learning what it can do ...even simple things seems harder

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 18:46:47

good luck!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-03 : 19:14:51

spirit1,

I figure out how to use two record sets with enterprise library, so I am following your suggestion,

so I join the tables

Select *
from Terms
join TermsRelated ON Terms.ID = TermsRelated.ID

but i am still unsure on what should my selects be to make two record sets ?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-03 : 19:23:04

Select *
from Terms

Select TR.*
from Terms T
join TermsRelated TR ON TID = TR.ID

also specify column names instead of the *

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

johnstern
Yak Posting Veteran

67 Posts

Posted - 2007-05-03 : 19:28:52

got it thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources