How to check the status column in a table in SQL Server 2008?

I need a query to check the status column in a table, if all the records have status completed means I have to set @flag=0. Table structure is:

ID |status   |date
--------------------
1  |complete |01-01-2011
2  |complete |02-02-2011
3  |start    |03-03-2011

As the 3rd record is start than I need to set @flag=1. I mean, if all the record开发者_如何学Gos are complete than the flag should be 0 else 1

IF EXISTS (
           SELECT * 
             FROM YourTable
            WHERE COALESCE(status, 'start') <> 'complete'
          )
   SET @flag = 1;
   ELSE SET @flag = 0;

You questions is not very clear, but I wonder if you might be looking for something like this:

declare @id int
set @id = 1
declare @flag int

select @flag = (case when status = 'complete' then 0 else 1 end) from [tablename] where id = @id

SET @flag = 0;
IF EXISTS(select 1 from YourTable y where y.[status] = 'start')
SET @flag = 1;