sql is there a command to check if a row is zero?

Question

I need to check if a row is zero. For nulls I usually use isnull() but my rows are never null. If there is such a command, I would use it like this:

select * from maintable where column1 = iszero(@param,column1)

I'm confused. Are you asking how to check if a column that contains integers contains a 0 value? — James Hill, Aug 08 '12 at 15:56
What do you mean "IsZero"? Why would you just not use `select * from maintable where column1 = 0?` — David, Aug 08 '12 at 15:57
maybe this is helpful: http://stackoverflow.com/q/580364/405117 — Vikram, Aug 08 '12 at 15:59

score 1 · Accepted Answer · answered Aug 08 '12 at 16:08

Assuming your looking for something to work in the same way as IsNull() you could define your own function

create function IsZero(@value int, @default int)
    returns int
as
begin

    if (@value <> 0)
        return @value

    return @default
end

Example of usage

select 
    Returns5 = dbo.Iszero(5, 10),
    Returns10 = dbo.Iszero(0, 10)

or in your case

select * from maintable where column1 = IsZero(@param,column1)

score 0 · Answer 2 · answered Aug 08 '12 at 16:00

SELECT * 
FROM   maintable 
WHERE  column1 = CASE @param 
                     WHEN 0 THEN column1 
                     ELSE @param 
                 END

This will give you the record in the maintable with where column1 equals the @param variable. If the @param variable is 0 then it will return all records in the maintable.

score 0 · Answer 3 · answered Aug 08 '12 at 16:07

0

Try this:

select *
from maintable
where column1 = @param or @param = 0

answered Aug 08 '12 at 16:07

Gordon Linoff

1,242,037
58
646
786

sql is there a command to check if a row is zero?

3 Answers3