very complicated stored proc
-
just guessing, but the whole SQL is full of inner joins... so that means if one of those tables in all the joins do not have a row to match , no results ill get returned; for example, are you sure that EVERY pod_Event has a matching relevant issue in pod_RelevantIssues ?
I would suggest changing all your INNER JOIN statements to LEFT JOIN statements and see if you get more results, then change any join that you KNOW will always have a matching record back to inner joins, and optional data should be left as part of the LEFT JOIN (LEFT JOIN=LEFT OUTER JOIN)
FROM pod_Events
INNER JOIN pod_Event_Schools ON pod_Events.id_e = pod_Event_Schools.id_e
INNER JOIN pod_Schools ON pod_Event_Schools.id_s = pod_Schools.id_s
INNER JOIN pod_Zone ON pod_Schools.id_z = pod_Zone.id_z
INNER JOIN pod_Event_RI ON pod_Events.id_e = pod_Event_RI.id_e
INNER JOIN pod_Event_Inv ON pod_Events.id_e = pod_Event_Inv.id_e
INNER JOIN pod_RelevantIssues ON pod_Event_RI.ri_id = pod_RelevantIssues.ri_id
INNER JOIN pod_Involvement ON pod_Event_Inv.i_id = pod_Involvement.i_id
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Sergiy
That was the problem. I had an inner join not returning anything, since the database had some corrupt data which I did not know of!
I cleaned up everything and started afresh, and now its working fine
-
-
Thanks for your help mate!
Cheers
-
You welcome!
Seems you're over the ditch.
If you are anyway refactoring this SP I would suggest to put all those joins into view (to have it written one time) and do conditional selects in SP from view.
Less typing, easier to read and much less place for silly errors.
_____________
Code for TallyGenerator -
Hi Sergiy
Is it possible to explain to me what you just told me since I am a newbie.
I know how to put the joins into view, but how to do conditional selects???
-
It's easy.
Type
CREATE VIEW dbo.<name>
AS
and put there whole block of those horrible
Select ...(including region, school, etc)
FROM ....
INNER JOIN ...
INNER JOIN ...
....
without WHERE clause.
After that in SP you don't need to repeat this block again, just
IF @region > 0
select ... (only fields you need to show)
from dbo.<name>
where Region = @region
ELSE
select ...
from dbo.<name>
Same way with other parameters.
_____________
Code for TallyGenerator
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply