| Author |
Topic |
|
jrlanders
Starting Member
15 Posts |
 Posted - 2004-07-23 : 11:39:50
|
| Hi All and thanks in advance.I need some guidance on this one. I am working on an ASP app connecting to a SQL DB.I need to perform a one to many relationship look up in a stored procedure.DB structure:PartnerParnter_ID intsever other fieds here as wellPartner_TypePartnrType_ID intPartnrType_Partner_ID intParntrType_Desc varchar(50)I need to search for * in partner record using the Partner_ID key then find all Partner_Types that where Partner_ID = PartnrType_Partner_ID.Simple I know. I know I can easily do so with an inner join. Here is the delima(msp). If I inner join and there are 3 Partner_Types that match the Partner_ID value... I get 3 records returned in the record set which means I have to loop through the recordset returned to the ASP page to get all the data.Is there a way to make all that happen in the Stored procedure then return only one record with all the necessary data to reduce the coding effort on the asp page?I hope that makes sense.Any help appreciated.Thank James |
|
|
JasonGoff
Posting Yak Master
158 Posts |
Posted - 2004-07-23 : 11:43:27
|
| You *should* loop through the results and process them accordingly. Changing the SQL to reduce ASP coding ???? Why ? |
 |
|
|
jrlanders
Starting Member
15 Posts |
Posted - 2004-07-23 : 11:47:50
|
| Thanks replying Jason.On your question... I was hopeing it might be a little faster doing it in on the SQL side reducing the number of records comming back to the ASP. I guess it makes sense to do the processing on the ASP side. I was hoping for an advantage on the SQL side is all.Thanks again. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 12:29:07
|
| How do you want the data to come back - a comma delimited list of Partner_Types in a single column for example?Kristen |
|
|
|
jrlanders
Starting Member
15 Posts |
Posted - 2004-07-23 : 12:43:18
|
| Yes that would be great Kristen. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-23 : 12:51:09
|
| I was afraid you would say that! ... I'll see what I can knock upKristen |
|
|
|
Kristen
Test
22859 Posts |
|
|
jrlanders
Starting Member
15 Posts |
Posted - 2004-07-23 : 13:26:22
|
| Thanks alot Kristen... Your the best!!I will check it out in a few minutes....James |
|
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-07-26 : 01:51:56
|
| Hi Kristen,I checked link provided by you.I could not understand following piece of codedeclare @list varchar(8000), @lasti intselect @list = '', @lasti = -1--here is the meat of the workupdate #workingtableset @list = list = case when @lasti <> i then vc else @list + ', ' + vc end, @lasti = iCan you tell me bit more what it is doing.ThanksManoj |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-26 : 01:59:07
|
| It's declaring local working variables (the ones which start with "@"), and the syntax "@list = list = case ..." (in the UPDATE statement) is storing the result [of the CASE statement] in the column "list" in the table "#workingtable" and also in the variable "@list".If that doesn't clarify please ask a more specific question.Krsiten |
|
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-07-26 : 18:26:17
|
| Hi Kristen,select@list = '',@lasti = -1what is select statement doin here. It has not got any from clause.ThanksManoj |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-26 : 18:51:40
|
| It is just setting the variables. You don't need a FROM when setting variables. You could use two SET statements as an alternative if that makes it easier to read. I prefer SELECT so that I can set multiple variables at the same time.SET @list = ''SET @lasti = -1Tara |
|
|
|
mk_garg20
Constraint Violating Yak Guru
343 Posts |
Posted - 2004-07-27 : 02:54:40
|
| Thanks Tara! |
|
|
|
|
One to many Stored Procedure help pls.