Checking if any column has value = 1 in SQL Table

Question

I want a select statement which will find me if there is any row with column expired value = 1.

This is what I am trying with no luck,

Declare @FruitID INT;
SET @FruitID = '2876';

 SELECT 
    dbo.IsFruitExpired(@FruitID, Fruits.CrateID) AS FruitExpired
 FROM 
    Fruits  WHERE   Fruits.ID = @FruitID

Result

FruitExpired
0
0
1
0

So I want to return 1 if there is any row with value = 1 otherwise return 0, I am going to add this select statement in a function.

USE CASE STATEMENT http://stackoverflow.com/questions/5487892/sql-server-case-when-or-then-else-end-the-or-is-not-supported — A_Sk, Mar 19 '15 at 09:20

Giorgi Nakeuri · Accepted Answer · 2015-03-19T09:44:51.307

1

You can simply apply MAX function to result. If there are just 1 and 0 then it will work. Note that if you return BIT type from function you will need to cast:

Declare @FruitID INT;
SET @FruitID = '2876';
DECLARE @Result AS int

 SELECT @Result = MAX(dbo.IsFruitExpired(@FruitID, Fruits.CrateID)) AS FruitExpired FROM 
    Fruits  WHERE   Fruits.ID = @FruitID

If BIT is returned:

 SELECT @Result = MAX(CAST(dbo.IsFruitExpired(@FruitID, Fruits.CrateID) AS INT)) AS FruitExpired
 FROM  Fruits  WHERE   Fruits.ID = @FruitID

Or you can do it with EXISTS:

IF EXISTS(SELECT * FROM Fruits WHERE dbo.IsFruitExpired(@FruitID, CrateID) = 1)
   SET @Result = 1
ELSE
   SET @Result = 0

edited Mar 19 '15 at 09:44

answered Mar 19 '15 at 09:33

Giorgi Nakeuri

35,155
8
47
75

max(), the simplest solution :) great. @Giorgi – A_Sk Mar 19 '15 at 09:45
I didn't tested them yet, but i'll accept it as a answer, thanks – Mathematics Mar 19 '15 at 09:46
@chridam, I didn't declare that. OP has declared. May be he has ID column as char, I don't know. – Giorgi Nakeuri Mar 19 '15 at 09:57

chridam · Answer 2 · 2015-03-19T09:55:07.790

If I got your question correctly, you want to check on the any column in the Fruits table, this can be possible with the use of dynamic sql and the case statement:

DECLARE @tb NVARCHAR(255), @sql NVARCHAR(MAX), @FruitID NVARCHAR(5);
SET @FruitID = '2876';
SET @tb = N'dbo.[Fruits]';

SET @sql = N'SELECT 
                CASE';

SELECT @sql = @sql + N' WHEN ' + QUOTENAME(name) + ' = 1 THEN 1'
    FROM sys.columns 
    WHERE [object_id] = OBJECT_ID(@tb);

SELECT @sql = @sql + N' ELSE 0
                   END AS FruitExpired
               FROM ' + @tb + '
               WHERE Fruits.ID = ' + @FruitID
EXEC sp_executesql @sql;

score 0 · Answer 3 · answered Mar 19 '15 at 09:33

0

Using this for now,

    Declare @FruitID INT;
    SET @FruitID = '2876';
    DECLARE @Result AS int

    IF
      (SELECT  COUNT(*)
          FROM Fruits
          WHERE dbo.IsFruitExpired(@FruitID, Fruits.CrateID) = 1 )
    = 1

     SET @Result = 0
   ELSE
     SET @Result = 1

   RETURN (@Result)

answered Mar 19 '15 at 09:33

Mathematics

7,314
25
77
152

This will return nothing if there are no rows with 1. Or it will return many rows if there are many rows with 1. Also edit your question rather then adding answer. – Giorgi Nakeuri Mar 19 '15 at 09:36
see my answer. There are 2 ways. – Giorgi Nakeuri Mar 19 '15 at 09:45

Checking if any column has value = 1 in SQL Table

3 Answers3