Set variable =NULL

  • Sam-170626

    Old Hand

    Points: 342

    In my stored proc, I'm trying to set/pass a datetime variable to NULL. Then I want to check if that variable is NULL. if yes then do A else do B. For some reason even though the value of the variable appears to be NULL it's evaluting the the NULL as false.

    here's my code

    spTest (@currentDate datetime, @BegDate datetime=NULL, @EndDate datetime =NULL)

    If @BegDate=NULL

           Select * From TableA Where EntryDate Between @BegDate and @EndDate

    Else

        Select * From TableA Where EntryDate Between @CurrentDate and @EndDate

     

    any ideas?

    sam

     

  • SQL ORACLE

    One Orange Chip

    Points: 27807

    CREATE PROC spTest (@currentDate datetime, @BegDate datetime=NULL, @EndDate datetime =NULL) AS

    If @BegDate IS NULL

           Select * From TableA Where EntryDate Between @BegDate and @EndDate

    Else

        Select * From TableA Where EntryDate Between @CurrentDate and @EndDate

  • Farrell Keough

    SSCoach

    Points: 17414

    Try this:

    DECLARE @BegDate datetime

    SET @BegDate = NULL

    IF @BegDate = NULL

         PRINT 'NULL equals NULL'

    IF @BegDate IS NULL

         PRINT 'NULL IS NULL'

    ELSE IF ISNULL( @BegDate, '01/01/1900') = '01/01/1900'

         PRINT 'NULL does not equal NULL'

     

    Did not see SQL ORACLE's post.  This basically shows you that NULL does not equal NULL. 

    I wasn't born stupid - I had to study.

  • Sam-170626

    Old Hand

    Points: 342

    it worked

     

    thank u

    sam

  • Ron McCullough

    SSC Guru

    Points: 63877

    From BOL

    When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To  [<>] comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • remitos

    Grasshopper

    Points: 17

    It didn't work

    Capture

    Thx

    sam

     

    • This reply was modified 1 week, 2 days ago by  remitos.
    • This reply was modified 1 week, 2 days ago by  remitos.
    Attachments:
    You must be logged in to view attached files.
  • Jeff Moden

    SSC Guru

    Points: 995116

    remitos wrote:

    It didn't work

    Capture

    Thx

    sam

    First of all, that's an Oracle error.  Oracle and SQL Server haven a whole lot of differences.  PL/SQL <> T-SQL.  Let's hope that whoever "SQL Oracle" is, also knows Oracle instead of just claiming to be an "oracle" at SQL.

    Second, there are multiple answers on this thread.  You need to post the code that caused the error.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply