Select Negative values only if exists in SQL Server

Question

i am new to sql server. i am having some problem when selecting values for report. i am having the following columns:

name, snap, current, percent.

i want to select only negative values if exists in the column "percent" if not select +ve values only.

for example:

if i am having -1, -10, 0, 10 then it should select only -1, -10.

i tried the following query, but its giving both +ve and -ve values.

selectname, snap, current, percent 
where percent = 
CASE WHEN percent < 0 then percent 
when percent > 0 then percent

i don't know how to use if else in where condition. Can anyone help me out ? Thanks in advance.

Solution 1

You can use IF EXISTS to check the condition and then get the desired result

IF EXISTS(SELECT * FROM TABLENAME WHERE percent < 0)
BEGIN
    SELECT [name], [snap], [current], [percent] FROM TABLENAME WHERE [percent] < 0
END
ELSE BEGIN
    SELECT [name], [snap], [current], [percent] FROM TABLENAME WHERE [percent] > 0
END

Solution 2

You can try using UNION ALL to separate two conditions. Due to them being opposite, only one part of query will return rows.

SELECT [name], [snap], [current], [percent]
FROM Table1
WHERE EXISTS (SELECT * FROM Table1 WHERE [percent] < 0) 
AND [percent] < 0

UNION ALL

SELECT [name], [snap], [current], [percent] 
FROM Table1
WHERE NOT EXISTS (SELECT * FROM Table1 WHERE [percent] < 0)
AND [percent] > 0

SQLFiddle DEMO

Demo with non-negative values