CODEWITHSUNDEEP
sqlsql-servert-sql
KavenM
KavenM

Reputation: 7

How to check against a Select statement Returning multiple Rows

Using an IF Statement to check if results from a select statement = 'N' However the select statement returns multiple rows, Therefore breaking the IF Statement

I've tried to store results in a variable:

Declare @SENT bit; SET @SENT = (SELECT AlrdySent FROM DeliveryNotesNotMatchingSO) IF @SENT = 'N'

    INSERT INTO DeliveryNotesNotMatchingSO
    ([Docnum],
    [AlrdySent])
    Select 
    t1.DocNum as [DocNum],
    'N' as [AlrdySent]
    from miro.dbo.DLN1 t0 
    join miro.dbo.odln t1 on t0.DocEntry = t1.DocEntry
    where day(t1.DocDate) = day(getdate())
    and month(T1.DocDate) = month(getdate())
    and year(T1.DocDate) = year(getdate())
    and t0.BaseEntry is null

    ---Statement in Question
IF (SELECT AlrdySent FROM  DeliveryNotesNotMatchingSO) = 'N' 
BEGIN
---CODE HERE

I expect the IF Statement to execute for each result in the select statement, guess I need to do this row by row somehow?

Is there perhaps a better way of logically checking the value of AlrdySent?

Upvotes: 0

Views: 458

Answers (1)

EzLo
EzLo

Reputation: 14189

This is the 1% of the cases where you actually need a cursor to cycle records, since you have to send emails through an SP (I assume that works mail by mail).

A CURSOR is an object that allows you to cycle through a set of rows, a row at a time. You can use the following simple cursor example:

DECLARE @ID INT
DECLARE @AdditionalParameter VARCHAR(1000)

DECLARE MailingCursor CURSOR FOR
    SELECT
        D.ID,
        D.AdditionalParameter
    FROM
        DeliveryNotesNotMatchingSO AS D
    WHERE
        D.AlrdySent = 'N'

OPEN MailingCursor
FETCH NEXT FROM MailingCursor INTO @ID, @AdditionalParameter

WHILE @@FETCH_STATUS = 0 -- While the last fetched row was successfully fetched
BEGIN

    EXEC dbo.uspSendMail
        @Input = @ID,
        @Recipient = @AdditionalParameter

    UPDATE D SET
        AlrdySent = 'Y',
        SentDate = GETDATE()
    FROM
        DeliveryNotesNotMatchingSO AS D
    WHERE
        D.ID = @ID -- Assumming its a PK

    FETCH NEXT FROM MailingCursor INTO @ID, @AdditionalParameter

END

CLOSE MailingCursor
DEALLOCATE MailingCursor

Upvotes: 2

Related Questions