How to return 1 if select count(*) is > 0

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

General SQL Server Forums

New to SQL Server Programming

How to return 1 if select count(*) is > 0

Author

Topic

mark1504
Posting Yak Master

103 Posts

Posted - 2008-04-23 : 17:45:59

I'm ashamed! I'm stuck on something so simple....

I want to return a value of 1 if count(*)>0 AND 0 if COUNT(*) is 0

I have currently have this below, but isn't there a better way?

SELECT cnt=CASE WHEN (SELECT COUNT(*) FROM MyTable)>0 THEN 1 ELSE 0 END

(The full code is rather more complex than this, but the problem is the same)

Any suggestions welcome.

Cheers!
Mark

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-23 : 17:58:22

select distinct (case when (select count(*) from dbo.table)>0 then 1 else 0 end)
from your table

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-23 : 18:49:59

Can you give us more context? That seems pretty straight forward:

SELECT CASE WHEN (SELECT COUNT(*) FROM MyTable) > 0 THEN 1 ELSE 0 END AS cnt

-- OR

SELECT CAST((SELECT COUNT(*) FROM MyTable) AS BIT) AS cnt

-- OR

IF EXISTS (SELECT * FROM MyTable)
BEGIN
	SELECT 1 AS cnt
END
ELSE
BEGIN
	SELECT 0 AS cnt
END