CODEWITHSUNDEEP
sql-serversql-server-2008
Mathematics
Mathematics

Reputation: 7628

Checking if any column has value = 1 in SQL Table

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.

Upvotes: 0

Views: 4350

Answers (3)

chridam
chridam

Reputation: 103375

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;

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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

Upvotes: 2

Mathematics
Mathematics

Reputation: 7628

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)

Upvotes: 0

Related Questions