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

Hai guys,

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

select Is_D开发者_运维知识库eleted from Stock where Stock.Mat_Id=1

alt text http://www.freeimagehosting.net/uploads/aaaff13d8a.jpg

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...

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

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

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
...