<> and = in where clause

Question

Post reply

<> and = in where clause

JKSQL

SSCrazy Eights

Points: 8481
More actions
March 20, 2015 at 1:23 pm

#302325

i am just double checking to make sure this has to be two stored procedures. I can't make this into one can I?
--Sproc 1
SELECT Count(FooID)
FROM [dbo].[Foo]
WHERE
[FooName] = ''";
--Sproc 2
SELECT Count(FooID)
FROM [dbo].[Foo]
WHERE
[FooName] <> ''";

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

roger.price-1150775 Default port Points: 1458 More actions · Answer 1

SELECT

SUM(CASE WHEN FooName = '' THEN 1 ELSE 0 END) AS [blank],

SUM(CASE WHEN FooName = '' THEN 0 ELSE 1 END) AS [notblank]

FROM Foo

This should result in a single scan of the table instead of 2 althuogh if the table is indexes as separate queries may be able to use the index where above will not.

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 2

I see what you are saying...I can just make it without the where clause. Thanks

ScottPletcher SSC Guru Points: 101119 More actions · Answer 3

Note that the original query and the new query will produce different results if the column can contain NULLs. You need to construct your final query to handle NULLs however you prefer to handle them (count or not count).

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".