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)

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

score 0 · Accepted Answer · answered Mar 11 '14 at 11:27

0

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.

answered Mar 11 '14 at 11:27

Damien_The_Unbeliever

234,701
27
340
448

Yup, this looks like it will work! Yeah, we don't want to send an email if there's even a single row in Invoice for that RunId with a Status other than Invoiced.. thanks! – karan k Mar 12 '14 at 13:32
One thing- what if I want to use each RunID in each email that I'm sending? It wouldn't make sense to send the emails without including this RunID. also, is that possible w/o cursors? – karan k Mar 12 '14 at 13:40
The reason I'm asking for alternate ways is because I'd seen many SO posts recommending Against it! – karan k Mar 12 '14 at 13:41
I'd say avoid cursors whilst you're creating a result set (like the above) that can be expressed in a set-based manner. But once you're needing to call a stored procedure once for each row *of that set* (such as `sp_send_dbmail`) then you're forced to use a cursor or something like it. It's the correct thing to do, [*in those circumstances*](http://stackoverflow.com/questions/6606709/iterate-through-rows-in-sql-server-2008) – Damien_The_Unbeliever Mar 12 '14 at 13:48

Alternate way to using cursor/loop in SQL

1 Answers1