Need help with Stored Procedure

Posted on 2011-10-08
Last Modified: 2012-05-12
Hello Experts,

I have the following Stored Procedure below that I need to retrieve ALL data based on the ghaoc_id value entered even if the value ghaocp_id is NULL from the HealthCourses_CourseSchedules table.

The attached Excel spreadsheet includes all Database schema along with real data that is in my system. You will see that I have NULL values for the ghaocp_id in the HealthCourses_CourseSchedules table. I still need to display ALL to the user even if ghaocp_id has  NULL value.

I'm also attaching my current Stored Procedure that I'm having the trouble with.

Thank in advance!!


@ghaoc_id int


SELECT cs.ghaoc_id, oc.ghaco_name, cs.ghaocp_id, cs.csch_startdate, cs.csch_starttime, cs.csch_endtime, hi.hmi_name, p.ghaocp_name
FROM HealthCourses_CourseSchedules AS cs
INNER JOIN HealthCourses_HighmarkInstructors AS hi
ON cs.hmi_id = hi.hmi_id
INNER JOIN HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS p
ON cs.ghaocp_id = p.ghaocp_id
INNER JOIN HealthCourses_GeneralHealthAwarenessOneCourses AS oc
ON cs.ghaoc_id = oc.ghaoc_id
WHERE cs.ghaoc_id = @ghaoc_id
ORDER BY ghaocp_name ASC

Open in new window

Question by:asp_net2
    LVL 1

    Accepted Solution

    You are using INNER JOIN which means it excludes records where one side is null or where there is no matching record on the joined table (because by default NULL won't match anything, even another NULL). You would want to use LEFT OUTER JOIN to return all records from HealthCourses_CourseSchedules, even where ghaocp_id is NULL or doesn't match the other table. If you want to actually join records based on NULL in the column you should do something with ISNULL.

    LVL 4

    Author Closing Comment


    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Suggested Solutions

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now