Writing a 'Case' statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Writing a 'Case' statement

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-01-26 : 11:38:34

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, Case @Other_Certificates When NULL THEN NULL ELSE '8' END)
         
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

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-26 : 11:44:57

like this:
Case when @Other_Certificates is NULL THEN NULL ELSE '8' END

Go with the flow & have fun! Else fight the flow