Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2010
    Posts
    26

    Unanswered: IF EXISTS syntax quick question / Agent Job

    Hi Everyone! SQL noob (but learning!) here.

    I have this snippet:

    DECLARE @date datetime
    SELECT DISTINCT c.[col1], EmailAddress
    FROM tblCrazy c
    INNER JOIN tblA a ON c.col1= a.col1
    WHERE a.AID = 464
    AND a.Created > @Date
    AND a.Created < DATEADD(DAY,1,@Date)
    AND c.PreferredCom = 'Postal'

    which works fine. I'm working on putting together a SQL Agent job, which I want to send an email when this above code returns a valid record. My thought was do to something like this (see below) and then email via a

    IF EXISTS (
    DECLARE @date datetime
    SELECT DISTINCT c.[col1], EmailAddress
    FROM tblCrazy c
    INNER JOIN tblA a ON c.col1= a.col1
    WHERE a.AID = 464
    AND a.Created > @Date
    AND a.Created < DATEADD(DAY,1,@Date)
    AND c.PreferredCom = 'Postal'
    )

    but it complains about syntax.

    I would do this and then email via the sp_send_dbmail command. I was thinking about doing it this way since (to my knowledge) the agent job can email only on failure, success, and completion. Even if the select statement doesnt return anything, the job completes, so it would email falsely.


    So this is a two part question: What is wrong with my syntax, and is this the best way to do this?

    Dustin

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your first query is valid but will never return any rows. Have you missed out some code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by pootle flump View Post
    Your first query is valid but will never return any rows. Have you missed out some code?
    sorry, excuse my ignorance, but why wouldnt it return anything?

    There are limited records in the table right now, but if I remove

    WHERE a.AID = 464
    AND a.Created > @Date
    AND a.Created < DATEADD(DAY,1,@Date)
    AND c.PreferredCom = 'Postal'

    then it returns a number of rows.

    The full Query is below:

    USE [XXXXX]
    GO

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    DECLARE @date datetime
    SELECT DISTINCT c.[col1], EmailAddress
    FROM tblClrazy c
    INNER JOIN tblA a ON c.col1= a.col1
    WHERE a.AID = 464
    AND a.Created > @Date
    AND a.Created < DATEADD(DAY,1,@Date)
    AND c.PreferredCom = 'Postal'
    Last edited by dah97765; 08-23-10 at 10:40. Reason: To Quote

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You declare @date but do not set it to a value. This means its value is NULL.

    There are no dates greater than NULL. There are no dates less than DATEADD(DAY,1,NULL). The query will never return a resultset.

    Code:
    DECLARE @date datetime
    
    SELECT  *
    FROM    (
                SELECT Created = '20120101'
            ) AS proof
    WHERE   Created > @Date
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by pootle flump View Post
    You declare @date but do not set it to a value. This means its value is NULL.

    There are no dates greater than NULL. There are no dates less than DATEADD(DAY,1,NULL). The query will never return a resultset.

    Code:
    DECLARE @date datetime
    
    SELECT  *
    FROM    (
                SELECT Created = '20120101'
            ) AS proof
    WHERE   Created > @Date
    Yup, you're right. I should have pointed out that this was a stored procedure that I'm converting to an agent job. In the SP a date variable is passed. I will just set the variable as the current date in the job for now.

    Also (dont laugh... this is where the noob comes into play) I figured out the IF EXISTS syntax error - I was not issuing any command afterwards to do after asking if it exists or not. ^^

    Thoughts on best practices as far as issuing an email command from an agent job?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, there's more wrong with your EXISTS statement as posted.
    It is best not to post code that is not your problem in reality (especially if you are new to things) since we've now spent a long time discussing something that exists on this forum only and not in your environment.

    send_dbmail is the correct sproc to use.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Aug 2010
    Posts
    26
    Quote Originally Posted by pootle flump View Post
    Actually, there's more wrong with your EXISTS statement as posted.
    It is best not to post code that is not your problem in reality (especially if you are new to things) since we've now spent a long time discussing something that exists on this forum only and not in your environment.

    send_dbmail is the correct sproc to use.
    Thanks for your help. The working IF EXISTS statement is below. I would have clarified more, but I don't think it was relevant to my original question about the syntax of EXISTS. It does exist in my environment, just not in the Agent Job.

    Thanks again.

    DECLARE @date datetime
    SET @date = GETDATE()

    IF EXISTS(
    SELECT DISTINCT c.[col1], EmailAddress
    FROM tblClaim c
    INNER JOIN tblA a ON c.col1= a.col1
    WHERE a.AID = 464
    AND a.Created > @Date
    AND a.Created < DATEADD(DAY,1,@Date)
    AND c.PreferredCom = 'Postal'
    )
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name='DevTest1',
    @recipients='test@test.com',
    @subject='Test message',
    @body='This is the body of the test message.'

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by dah97765 View Post
    I would have clarified more, but I don't think it was relevant to my original question about the syntax of EXISTS.
    That's my point - if something is not working and you don't know why then post exactly what is failing. Certainly if you do post a selective portion of code you should test it to be absolutely certain it does exactly what you say it does.

    Anyway, now you have moved the declaration out of the query your SQL now looks good.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •