Check whether a value is present in a column or not in sql server 2005

Question

Hai guys,

I have a table with a column named Is_Deleted and my query is

select Is_Deleted from Stock where Stock.Mat_Id=1

Now i have to write a condition to check whether all values are 1 else i have to terminate my loop.. How it can be done? any suggestions...

Answer 1

select exists(select 1 from Stock where Mat_Id = 1 and
  (is_deleted is null or is_deleted <> 1))

Answer 2

This should do what you're after.

IF EXISTS(SELECT 1 FROM (select DISTINCT Is_Deleted from Stock where Stock.Mat_Id=1) a WHERE Is_Deleted <> 1)
BEGIN
 -- Terminate the loop
END
ELSE
BEGIN
 -- Perform action
END

Answer 3

A simple exists is all you need

IF EXISTS (SELECT * FROM Stock.Is_Deleted = 0 AND Stock.Mat_Id = 1)
...

If you are looping through different Mat_Ids...

...
--Get first @Mat_Id
WHILE @Mat_Id IS NOT NULL
BEGIN
    IF EXISTS (SELECT * FROM Stock.Is_Deleted = 0 AND Stock.Mat_Id = @Mat_Id)
    BEGIN
       ...
    END
    SET @Mat_Id = NULL
    --Get next @Mat_Id
END
...