please help with case statement 1

[developer77]

Hello everyone,

I have the following stored procedure that passes a @degree paramter:

CREATE PROCEDURE students_enrolled
(

@degree = null,
@ = null

)
AS
--
BEGIN

SELECT s.FirstName,
s.LastName,
s.MiddleName,
e.student_id,
e.degree
FROM tbl_Student s
left join tbl_Enrollment e on e.id = s.EnrollID
WHERE s.active_flg = 'Y'
AND e.degree LIKE CASE WHEN LEN(rtrim(ltrim(@degree))) > 0 THEN '%' + @degree + '%' ELSE e.degree END
RETURN
GO

In the database for tbl_Enrollment, sometimes the degree column is NULL. If the column is NULL, the records are not returned. Can you help me change this so that even when the values are NULL for degree, it still shows up the records and just displays the degree column as null?

Thanks

 

[gmmastros]

Maybe this...

WHERE   s.active_flg = 'Y'
        AND
          [!](e.degree is null  
           Or [/!]e.degree LIKE '%' + @degree + '%'[!])[/!]

With this where clause NULL degrees will be returned (as long as active_flg = 'Y').

I may have misunderstood your requirements, so you may want to also try....

WHERE   s.active_flg = 'Y'
        AND
          ([!]@degree[/!] is null  
           Or [/!]e.degree LIKE '%' + @degree + '%')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[developer77]

Hi George,

Thank you for your prompt response. I think the code you provided will work but if I don't enter any parameters for degree, I want it to be able to pull all records in the degree column including null. It looks like the code you provided will either pull based on the paranter entered or a null value. How can I get it to pull everything including NULL values? Unless a parameter is entered, then it should pull based on the paramter value given. Hope this makes sense.

Thanks

 

[gmmastros]

It sounds like you want to use the 2nd example.

WHERE   s.active_flg = 'Y'
        AND
          (@degree is null  
           Or e.degree LIKE '%' + @degree + '%')

If the parameter is NULL, then all records will be returned (where active_flg='Y'). If the parameter is not NULL, then only those records that match the parameter value will be returned.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[developer77]

George, thanks for your help...it works!!! Sorry I misunderstood it the first time...