CODEWITHSUNDEEP
sqlsql-server-2012
Austin
Austin

Reputation: 154

Clean up SQL Code

I have created a working SQL query but it's ugly. I am using the statement:

CASE WHEN p.Guests is null THEN 0 ELSE p.Guests

About 10 times in some calculations being done in the query and I am wondering if it is possible to do something like:

variable = CASE WHEN p.Guests is null THEN 0 ELSE p.Guests

and then instead of 123 * (CASE WHEN p.Guests is null THEN 0 ELSE p.Guests) I could do 123 * variable. p.Guests is the number of guests attending an event and it varies based on each row in the query. So my question is: is there a way to make a variable like this in SQL?

Upvotes: 2

Views: 245

Answers (2)

Mike Christensen
Mike Christensen

Reputation: 91666

You can use ISNULL:

select 123 * ISNULL(p.Guests,0) FROM ...

The ISNULL function will return the first parameter, unless it is null in which case it will return the second parameter.

Upvotes: 3

Marc B
Marc B

Reputation: 360762

No need for a long-winded case statement when there's

ISNULL(p.Guests, 0)

http://msdn.microsoft.com/en-us/library/ms184325.aspx

Upvotes: 3

Related Questions