Hi all,I am trying to amend an existing sproc on SQLServer 2005 to return an extra value from additionally joined table, however the read on the extra table is conditional. Existing select is as follows - SELECT a.[ID], a.Profile, ns.Code, AB.AmountFROM [dbo].[Addition] aLEFT OUTER JOIN AdditionBids AB ON AB.ID = A.IDLEFT OUTER JOIN [NotificationsLoad].NSemail ns ON a.ProfileID = ns.SubscriberIDWHERE a.ID = @ID;
What I want to do is something along these lines returning a new value c.Path - SELECT a.[ID], a.Profile, ns.Code, AB.Amount, c.PathFROM [dbo].[Addition] aLEFT OUTER JOIN AddBids AB ON AB.ID = A.IDLEFT OUTER JOIN [NotificationsLoad].NSemail ns ON a.ProfileID = ns.SubscriberID--my additionsJOIN Additionbatch ADB on ADB.BatchID = a.BatchIDif 0 = select * from dbo.ContentAncestors CA where CA.ContentID = ADB.ContentID AND CA.Type = 2beginJOIN [dbo].[Content] c on c.ContentID = ADB.ContentIDendelsebeginselect CA.ContentID from ContentAncestors CA where CA.ContentID = ADB.ContentID and CA.AncestorType = 2;JOIN [dbo].[Content] c on c.ContentID = CA.ContentIDend--my additions endWHERE a.ID = @ID;
I have to do an additional join on the Additionbatch table to get the ContentID which I want to use in my next join. It's this next join that's giving me a headache. When the if statement doesn't return any rows then I can go ahead and join on Content for that contentID and return the c.Path value for the returned row on the Content table. Otherwise I have to get a different contentID from ContentAncestors and join on that.Obviously my sql here is going to throw a load of errors but I hope it shows what I am trying to achieve. It's been alot of years since I've looked at stored procedures and sql in general and that was DB2 on a mainframe rather than SQlServer. I'd be hugely grateful if a wiser head than mine (and that's pretty much everyone) could point me in the right direction on this. I hope my explanation is n't too confusing.Also a couple of general questions while I am on - I notice that whoever wrote the existing sproc has sometimes used square brackets around database objects (tables and attributes) and sometimes specifies [dbo]. before tables. Is this just a convention? Does it change anything/optimise anything in the RDBMS?If the left outer join preserves all the unmatched rows from the first (left) table and matches them with a null in the second, what is join on it's own returning? I hope I am using it correctly here.Huge thanks in advance to anyone who takes the time out to reply to any of this, it's really appreciated.
Help with select clause on sproc - if/else logic
Posted - 2010-11-21 : 08:31:16
