Help - Conditional Checks within a SQL Query - other ways of doing it?

I'm trying to simplify a SQL Stored Procedure.

The query accepts an int, @.ItemTypeID int

I have the Query:

SELECT ... FROM ItemList WHERE ItemTypeID = @.ItemTypeID

or, if @.ItemTypeID is 0,

SELECT ... FROM ItemList

Is there a way to do this query without doing:

IF @.ItemTypeID = 0
BEGIN
...SELECT QUERY...
END
ELSE
BEGIN
...SELECT QUERY...
END

?

Try if you can combine the query by using joins. If not, then stay with the current. A Sql stored procedure is not meant to be very readable like C#, it goes by the execution plans. Hence even the dynamica sql should be used reluctantly.

|||

select... from itemList where (@.itemTypeID = 0) or (@.ItemTypeID = ItemTypeID)

|||

Yes, although what you have will execute the fastest.

If you feel it's not worth the performance difference, you can always do this:

SELECT * FROM ItemList WHERE (ItemTypeID=@.ItemTypeID OR @.ItemTypeID=0)

|||

Ok, thanks guys.

I guess the extra

WHERE (ItemTypeID=@.ItemTypeID OR @.ItemTypeID=0)

Adds a check on each row that is returned - which slows it down. I'll keep the current ugly method :)

Just sometimes I might have to check 5-6 things... which looks very ugly.

|||

I'd be amazed if you could measure the difference.

Hello? Left Join?

Am I missing something? I thought LEFT JOIN made the first table return a value even if there was nothing in the second table. Yet this query:

SELECT b.name, isnull(c.call_no, 0)

FROM business b

LEFT JOIN call c ON b.business_id=c.service_business_id

WHERE b.business_id = 1000634

AND c.create_time BETWEEN @.startDate AND @.endDate

Is not returning any rows at all because there are no calls during the time period I'm using.

The query works if I extend the date range to include at least one call, so I know the business id and everything is correct...

Am I losing my mind? Or have I completely misunderstood what LEFT JOIN is supposed to do?

Hi Telos

You need to re-arrange your query slightly, see below.

Chris

SELECT b.name, isnull(c.call_no, 0)

FROM business b

LEFT JOIN call c ON b.business_id=c.service_business_id AND c.create_time BETWEEN @.startDate AND @.endDate

WHERE b.business_id = 1000634

|||

Ok, that works.

I don't understand why though... can I get an explanation?

|||

The reason it didn't work before is that in case of OUTER JOINS the WHERE clause is applied after the ON clause. If you have rows that doesn't satisfy the condition in the ON clause the column values will be NULL and the BETWEEN check will fail (or evaluate to unknown and hence the row(s) will not qualify).

But the way to write the query is to use a simple sub-query in the SELECT list. There is really no reason to use outer join. You need to use outer join construct if you are retrieving more columns from the outer joined table for example. Otherwise, a sub-select is the way to go.

SELECT b.name

, coalesce((select c.call_no

from call c

where b.business_id=c.service_business_id

and c.create_time BETWEEN @.startDate AND @.endDate), 0) as call_no

FROM business b

WHERE b.business_id = 1000634

|||

Ok, that makes sense.

The real query is a bit more complex though, so I was trying to avoid the subselect. The one Chris posted is working well.

Thanks for your help!

|||

The other responses are great. I just thought I would add that in your original query you could have put

AND ((c.create_time BETWEEN @.startDate AND @.endDate)

OR (c.create_time IS NULL)

)

That way your WHERE clause accommodates the fact that the OUTER JOIN may not return a value for c.create_time.

|||

The subtle difference between DanR1's approach and the others is that if the value stored in the table in the c.create_time column is NULL then the row will qualify and, therefore, will be returned. However if the c.create_time column is not nullable then this query will behave in the same way as the others.

Chris

|||

Chris,

Thanks for the elaboration.

Dan