CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2005sql-server-2008-r2
Dev
Dev

Reputation: 6710

SELECT 1 FROM [SystemEventLog]

I am using SQL Server 2008 R2.

I do have an SQL Job that executes a stored procedure daily 8:00 AM.

In stored, there is a statement : 

    DECLARE @midnight datetime,
    @startTime datetime = GETUTCDATE();

    -- Has this stored procedure already run for today?
    IF EXISTS (SELECT 1 FROM [SystemEventLog] WHERE [CreatedDateTime_UTC] >= CAST(@midnight AS DATE) AND [CreatedDateTime_UTC] < DATEADD(DD, 1, CAST(@midnight AS DATE)))
    BEGIN
         RETURN
    END


-- log that this storedproc ran
DECLARE @SystemEventLogId int 
INSERT INTO [dbo].[SystemEventLog] ( [EventType_Id], [StoredProcName], [Details] )
VALUES( 1, 'StoredProcedureOne', null )

According to the comment, this block is checking that whether this store procedure has run today or not, if yes then return.

But I am not getting how does it checks whether this procedure has alreday ran today or not?

Can anyone explain?

Thanks.

Upvotes: 0

Views: 46

Answers (1)

Adrian Wragg
Adrian Wragg

Reputation: 7401

Breaking that stored procedure down ...

SELECT 1 FROM [SystemEventLog]
    WHERE [CreatedDateTime_UTC] >= CAST(@midnight AS DATE)
      AND [CreatedDateTime_UTC] < DATEADD(DD, 1, CAST(@midnight AS DATE))

This will output a single row (the 1 is irrelevant - it's just content) if that condition is met.

Then ...

IF EXISTS ( [statement] )

is true if [statement] returns at least one row. If it is true, the stored procedure exits (through the RETURN statement); if false, it will continue.

The part missing from your example would be below this, where it will then add a new line to SystemEventLog that will - on later executions - then make the condition true for the rest of the day.

Upvotes: 2

Related Questions