T-SQL Query null issue-CodePudding

I want to write a store procedure which will return true or false based on the following condition

if 2 columns in all the rows in a result set are null then return true , if at least one is not null then return false Like for example in the following query

select * 
from Products 
where productID=123 and ProductType is null and ProductDate is null

The above query can return 1 row or return 100 rows , so if all are null then it will return true. if at least 1 is not null then it will return false.

Any help would be greatly appreciated

CodePudding user response：

select IIF(COUNT(*) = 0, 'true','false')
from Products 
where productID=123 and ProductType is null and ProductDate is null

CodePudding user response：

Try:

IF EXISTS (SELECT 1 FROM Products WHERE ProductID = 123 AND (ProductType IS NOT NULL OR ProductDate IS NOT NULL))

SELECT CAST (0 AS bit)

ELSE

SELECT CAST (1 AS bit)

Using exists is more efficient than a count since it will only scan the table until the condition is met. Count will always scan the whole table.

CodePudding user response：

We can do:

CREATE PROCEDURE dbo.whatever 
AS    
  DECLARE @return int = CASE WHEN EXISTS (SELECT 1
    FROM dbo.Products 
    WHERE productID = 123
      AND (ProductType IS NOT NULL OR ProductDate IS NOT NULL)
  ) THEN 0 ELSE 1 END;

  RETURN @return;
END

Then to execute:

DECLARE @hr int;
EXEC @hr = dbo.whatever;
PRINT @hr;

The tricky bit is when you say "return" we're not quite sure if you mean the explicit RETURN keyword, or an OUTPUT parameter, or a resultset. I feel like an OUTPUT parameter would be more appropriate in this case, since RETURN is generally reserved for error/status (and can only be an int). So we can use a more appropriate type depending on what we need:

CREATE PROCEDURE dbo.whatever 
  @return bit OUTPUT = NULL
AS    
  SET @return = CASE WHEN EXISTS (SELECT 1
    FROM dbo.Products 
    WHERE productID = 123
      AND (ProductType IS NOT NULL OR ProductDate IS NOT NULL)
  ) THEN 0 ELSE 1 END;

Then to execute:

DECLARE @return bit;
EXEC dbo.whatever @return = @return OUTPUT;
PRINT @return;