How can I get all rows of data not included in a TVP

Question

I am trying to do the following:

declare @domains DomainNames;
declare @CompanyId int
declare @CompanyName varchar(255);
declare @deletedList DomainNames;

set @CompanyId = 239;
set @CompanyName = 'dave';
insert into @domains (Domain) values ('ev2.com'), ('ev3.com');

select * from CompanyDomains where CompanyId = @CompanyId and EmailDomain != @domains

where I get back all rows in the table that do not have an EmailDomain value matching any of the values in @domains (and the CompanyId has a set value). Is there a way to do this?

Sql Server 2008 & Sql Azure.

thanks - dave

Answer 1

Using NOT EXISTS should work, eg

SELECT *    --!!TODO add column list
FROM dbo.CompanyDomains cd 
WHERE CompanyId = @CompanyId 
AND NOT EXISTS ( 
    SELECT *
    FROM @domains t
    WHERE cd.Domain = t.domain
    )