Alternate way to using cursor/loop in SQL

Question

I want to see if there is any way to do what I'm trying to do without using cursors, or any looping logic. I have these 2 tables. There is a ONE-MANY relationship between FileRunData and Invoice, and they're linked by RunId/FileRunId.

CREATE TABLE [dbo].[FileRunData](
    [RunId] [uniqueidentifier] primary key,
    [Status] [varchar](25) NOT NULL,
    [IsEmailSent] [bit] NOT NULL
)

CREATE TABLE [dbo].[Invoice](
    [FileRunId] [uniqueidentifier] NULL,
    [InvoiceId] [uniqueidentifier] primary key,
    [InvoiceType] [varchar](20) NOT NULL,
    [Status] [varchar](25) NULL
)

I want to send an email notification for the following condition.

In the FileRunData if Status='Processed' and IsEmailSent=0, then I have to check all the rows in the Invoice table for that FileRunId, and if their Status is 'Invoiced', then I have to send an email.

My approach (will be using cursor here)

1. select RunIds from FileRunData
2. for each RunId, get the number of rows for that RunId from Invoice
3. get the number of rows for that RunId which have Status='Invoiced'
4. If both 3&4 are equal, then send the email

Answer 1

I think by adding a couple of negations in, the problem is simpler to understand, unless I've severely misunderstood you. We don't want to send an email if there's any row in Invoice which has a status other than Invoiced:

SELECT frd.RunId
FROM FileRunData frd
WHERE frd.Status = 'Processed' AND
      frd.IsEmailSent = 0 AND
      NOT EXISTS (select * from Invoice i
                   where i.FileRunID = frd.RunID and
                         i.Status != 'Invoiced')

should select the IDs for runs which meet your conditions. Unfortunately, if you need to use these RunId values and send one email for each such result, then unfortunately, at this point you'll still have to use a cursor¹ to process this result set and make the actual sp_send_dbmail calls.

---

¹Or any morally equivalent way of processing each row in turn. I'd normally just use a cursor but some people have an aversion to the word even appearing in their SQL and insist on creating temp tables and while loops instead, for example.