Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Stored Procedure Help!

Posted on 2011-10-31
14
Medium Priority
?
213 Views
Last Modified: 2012-05-12
Hello Experts,

I have the following Stored Procedure below that is not working to my needs and need some help. I have attached an Excel file that has data for the two tables that I'm working with.

I need to see the following fields below based on two values shown below.

Note: the value ghaocp_id may be NULL or = 0 in some situations. Also, all ghaocp_id values are all related to a particualar ghaoc_id value.

Values:
ghaoc_id
ghaocp_id

Fields to display:
ghaco_name  --> From HealthCourses_GeneralHealthAwarenessOneCourses Table
ghaocp_name  --> From HealthCourses_GeneralHealthAwarenessOneCoursesPrograms Table

ALTER PROCEDURE [dbo].[HealthCourses_RetrieveRegisterCourseInfo]

(
@ghaoc_id int,
@ghaocp_id int
)

AS

SELECT oc.ghaoc_id, oc.ghaco_name, ocp.ghaocp_id, ocp.ghaocp_name
FROM dbo.HealthCourses_GeneralHealthAwarenessOneCourses AS oc
INNER JOIN dbo.HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS ocp
ON oc.ghaoc_id = ocp.ghaoc_id
WHERE oc.ghaoc_id = @ghaoc_id

Open in new window

ee.xlsx
0
Comment
Question by:asp_net2
  • 6
  • 5
  • 3
14 Comments
 
LVL 25

Expert Comment

by:TempDBA
ID: 37058530
so basically you want to get the data even if ghaocp_id is null. For that, you need to change your inner join with left outer join.

ALTER PROCEDURE [dbo].[HealthCourses_RetrieveRegisterCourseInfo]

(
@ghaoc_id int,
@ghaocp_id int
)

AS

SELECT oc.ghaoc_id, oc.ghaco_name, ocp.ghaocp_id, ocp.ghaocp_name
FROM dbo.HealthCourses_GeneralHealthAwarenessOneCourses AS oc
Left Outer JOIN dbo.HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS ocp
ON oc.ghaoc_id = ocp.ghaoc_id
WHERE oc.ghaoc_id = @ghaoc_id
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37058559
Hi TempDBA,

Ok, here is the problem that I keep having. If I execute that SP like I did with mine then I get the following results below when I enter value 301 for ghaoc_id and value 10009 for ghaocp_id.

I should ONLY see Eat Well For Life I for ghaoc_name and Online with Pam Brown for ghaocp_name but instead I get all three results which is NOT correct.

ghaoc_id       ghaoc_name                      ghaocp_id    ghaocp_name
301      Eat Well For Life I      10007      Live & Online
301      Eat Well For Life I      10008      Online with Jennifer Diemert
301      Eat Well For Life I      10009      Online with Pam Bown
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37058574
in that case, just change this at the where clause:

WHERE oc.ghaoc_id = @ghaoc_id
and ocp.ghaocp_id = @ghaocp_id
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Author Comment

by:asp_net2
ID: 37058679
Hi TempDBA,

Yes, that part I get but my problem is that I may NOT have a value for ghaocp_id and only a value for ghaoc_id. I tried executing the SP with no value for ghaocp_id and I get the following error below.

Msg 201, Level 16, State 4, Procedure HealthCourses_RetrieveRegisterCourseInfo, Line 0
Procedure or function 'HealthCourses_RetrieveRegisterCourseInfo' expects parameter '@ghaocp_id', which was not supplied.
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37058705
Do something as below:-

ALTER PROCEDURE [dbo].[HealthCourses_RetrieveRegisterCourseInfo]

(
@ghaoc_id int,
@ghaocp_id int = NULL
)

AS

SELECT oc.ghaoc_id, oc.ghaco_name, ocp.ghaocp_id, ocp.ghaocp_name
FROM dbo.HealthCourses_GeneralHealthAwarenessOneCourses AS oc
INNER JOIN dbo.HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS ocp
ON oc.ghaoc_id = ocp.ghaoc_id
WHERE oc.ghaoc_id = @ghaoc_id
and ocp.ghaocp_id = isnull (@ghaocp_id,ocp.ghaocp_id)
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37058765
Hi TempDBA,

That did NOT work. I executed the SP and entered a value of 308 for ghaoc_id and NOTHING was retrieved :(
0
 
LVL 25

Expert Comment

by:TempDBA
ID: 37058923
Its not because you are passing 308 for which ghaocp_id doesn't exist. So, combine both the solutions I provided above i.e. the last one modified with left outer join.

ALTER PROCEDURE [dbo].[HealthCourses_RetrieveRegisterCourseInfo]

(
@ghaoc_id int,
@ghaocp_id int = NULL
)

AS

SELECT oc.ghaoc_id, oc.ghaco_name, ocp.ghaocp_id, ocp.ghaocp_name
FROM dbo.HealthCourses_GeneralHealthAwarenessOneCourses AS oc
left outer JOIN dbo.HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS ocp
ON oc.ghaoc_id = ocp.ghaoc_id
WHERE oc.ghaoc_id = @ghaoc_id
and ocp.ghaocp_id = isnull (@ghaocp_id,ocp.ghaocp_id)
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37058983
That still does not work. If I enter 308 for ghaoc_id and leave ghaocp_id blank then NO VALUES return when in fact I should have a value of Drop 10 in 10 for ghaco_name. No matter if I use Left, Right, Inner, or outer joins that still does not display the data that I need.
0
 
LVL 25

Accepted Solution

by:
TempDBA earned 1000 total points
ID: 37060775
ALTER PROCEDURE [dbo].[HealthCourses_RetrieveRegisterCourseInfo]

(
@ghaoc_id int,
@ghaocp_id int = NULL
)

AS

SELECT oc.ghaoc_id, oc.ghaco_name, ocp.ghaocp_id, ocp.ghaocp_name
FROM dbo.HealthCourses_GeneralHealthAwarenessOneCourses AS oc
left outer JOIN dbo.HealthCourses_GeneralHealthAwarenessOneCoursesPrograms AS ocp
ON oc.ghaoc_id = ocp.ghaoc_id
WHERE oc.ghaoc_id = @ghaoc_id
and isnull(ocp.ghaocp_id,1) = isnull (@ghaocp_id,isnull(ocp.ghaocp_id,1))
0
 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 1000 total points
ID: 37062037
try this...



ALTER PROCEDURE [dbo].[HealthCourses_RetrieveRegisterCourseInfo]

(
@ghaoc_id int,
@ghaocp_id int = null
)

AS

SELECT oc.ghaoc_id, oc.ghaco_name, ocp.ghaocp_id, ocp.ghaocp_name
FROM dbo.HealthCourses_GeneralHealthAwarenessOneCourses AS oc
Left outer JOIN (select 
                   from dbo.HealthCourses_GeneralHealthAwarenessOneCoursesPrograms
                  where @ghaocp_id is null
                     or ghaocp_id = @ghaocp_id
                ) AS ocp
  ON oc.ghaoc_id = ocp.ghaoc_id
WHERE oc.ghaoc_id = @ghaoc_id

Return

Open in new window

0
 
LVL 4

Author Comment

by:asp_net2
ID: 37062144
Hi Lowfatspread,

When I execute your SP above I get the following error message below.

Msg 156, Level 15, State 1, Procedure HealthCourses_RetrieveRegisterCourseInfo, Line 13
Incorrect syntax near the keyword 'FROM'.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37062218
change line 12 to

Left outer JOIN (select *
0
 
LVL 4

Author Comment

by:asp_net2
ID: 37062282
@Lowfatspread,

Yes, that worked this time. If you don't mind can you explain the difference between what you supplied in 37062037 compared to what TempDBA supplied in 37060775? Both of your solutions work fine. But they look completely different and I would just like to know what that is and the difference between the two. Also, is there a more performance gain between either or?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37062652
performance wise difficult to say...

the use of a function (isnull in this case) call could reduce the efficiency of the access plan generated due to sargability issues,
the database engine will have during parsing of the query... and so lead the engine not to effectly use an index in certain situations...

(it depends on the sizes of the tables concerned..)


however the difference in the queries  are just a matter of writing style... i prefer to write and join subqueries as that way
you don't need to cater for null situations when writing the overall where clause for the query (ie i don't have to say ghaocp_id = @ghaocp_id or ghaocp_id is null.. ), and it avoids confusion over wether the Null is the result of a Null data value or a non existant row in the join..

hth







0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

864 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