Writing a ‘Case’ statement 3

[cesark]

Is this syntax correct? (The part in bold)

...

SET NOCOUNT ON

-- These are delcared output parameters 
 
        SELECT @Certificate_Gest0 = Certificate_Gest1, @Certificate_Gest1 = Certificate_Gest2,
        @Certificate_Gest2 = Certificate_Gest3, @Certificate_Gest3 = Certificate_Gest4,
        @Certificate_Gest4 = Certificate_Gest5, @Certificate_Gest5 = Certificate_Gest6, 
        @Certificate_Gest6 = Certificate_Gest7, @Other_Certificates = Other_Certificates

        FROM CertGest_Offer
        WHERE Offer_num = @Offer_id
         
SET NOCOUNT OFF


SET NOCOUNT ON

        SELECT Certificate_id, Certificate_name 
        FROM Certificates
        WHERE Certificate_id IN (@Certificate_Gest0, @Certificate_Gest1, @Certificate_Gest2, @Certificate_Gest3, @Certificate_Gest4, @Certificate_Gest5, @Certificate_Gest6, [B]Case @Other_Certificates When NULL THEN NULL ELSE '8' END[/B])
         
SET NOCOUNT OFF
GO

I want when the output parameter @Other_Certificates is Null return ‘null’ so that none Certificate_id is found, and when it is not Null return number 8 so that the Certificate_id number 8 is found. How can I do it?

Thank you,
Cesar

 

[swampBoogie]

Case when @Other_Certificates is not NULL then '8' END

 

[checkai]

...

SET NOCOUNT ON

-- These are delcared output parameters 
 
        SELECT @Certificate_Gest0 = Certificate_Gest1, @Certificate_Gest1 = Certificate_Gest2,
        @Certificate_Gest2 = Certificate_Gest3, @Certificate_Gest3 = Certificate_Gest4,
        @Certificate_Gest4 = Certificate_Gest5, @Certificate_Gest5 = Certificate_Gest6, 
        @Certificate_Gest6 = Certificate_Gest7, @Other_Certificates = Other_Certificates

        FROM CertGest_Offer
        WHERE Offer_num = @Offer_id
         
SET NOCOUNT OFF


SET NOCOUNT ON

        SELECT Certificate_id, Certificate_name 
        FROM Certificates
        WHERE Certificate_id IN (@Certificate_Gest0, @Certificate_Gest1, @Certificate_Gest2, @Certificate_Gest3, @Certificate_Gest4, @Certificate_Gest5, @Certificate_Gest6, ISNULL(@Other_Certificates,8))
         
SET NOCOUNT OFF
GO

 

[stephlal]

I rarely write code with declares, but use the CASE statement all the time. I only use the CASE statement in the SELECT condition. So I don't know if having a CASE statement in the WHERE condition will work. Again because of your declares, I'm unsure where you would put your case statement. But here's the syntax in bold with no declares:

([b]CASE WHEN[/b] Other_Certificates IS NULL [b]THEN[/b] NULL [b]ELSE[/b] '8' END)

You have the WHEN in the wrong place and you should always enclose your case statements with ( )

I assume if you wanted to do the samething with declares, it would look like this:

SET NOCOUNT ON

-- These are delcared output parameters 
 
        SELECT @Certificate_Gest0 = Certificate_Gest1, @Certificate_Gest1 = Certificate_Gest2,
        @Certificate_Gest2 = Certificate_Gest3, @Certificate_Gest3 = Certificate_Gest4,
        @Certificate_Gest4 = Certificate_Gest5, @Certificate_Gest5 = Certificate_Gest6, 
        @Certificate_Gest6 = Certificate_Gest7, @Other_Certificates = Other_Certificates

        FROM CertGest_Offer
        WHERE Offer_num = @Offer_id
         
SET NOCOUNT OFF


SET NOCOUNT ON

        SELECT Certificate_id, Certificate_name 
        FROM Certificates
        WHERE Certificate_id IN (@Certificate_Gest0, @Certificate_Gest1, @Certificate_Gest2, @Certificate_Gest3, @Certificate_Gest4, @Certificate_Gest5, @Certificate_Gest6, [b](CASE WHEN @Other_Certificates IS NULL THEN NULL ELSE '8' END)[/b])
         
SET NOCOUNT OFF
GO

If that doesn't work, then re write your query to have the CASE statement in your select condition. Again, the CASE statement might work in the WHERE condition, I've just never used it there. And well, your declares are throwing me off, so yeah...just trying to help...

Let me know what works, if anything...

 

[SQLSister]

stephlal, you most defintely can use case in a where clause. Your solution should work unless Certificate_id is one of the numeric datatypes. In that case, just remove the apostrophes around the 8.

Checkai's code should have the oppposite effect of the what cesark seemed to be asking for. That code would replace the null with 8. But the original post seemed to indicate that null should stay null, it was any other value that needed to be replaced.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[cesark]

That's right SQLSister.

Now it works in this way:

..., (CASE WHEN @Other_Certificates IS NULL THEN NULL ELSE '8' END))

The Certificate_id datatype is smallint

Thank you to all!