Professional Web Applications Themes

how to determine whether a field has null value - Microsoft SQL / MS SQL Server

Dear SQL Server Programmers, Please consider the following SQL statement: SELECT DischargeDate FROM MedicalFollowUpInHospital WHERE OurFileNo = 'R03000003' AND DischargeDate IS NULL I would like to write a stored procedure whic returns 1 if there's a null value for DischargeDate and 0 if there isn't. I couldn't manage that. Thanks in advance, Buran...

  1. #1

    Default how to determine whether a field has null value

    Dear SQL Server Programmers,

    Please consider the following SQL statement:

    SELECT DischargeDate FROM MedicalFollowUpInHospital
    WHERE OurFileNo = 'R03000003'
    AND DischargeDate IS NULL

    I would like to write a stored procedure whic returns 1 if there's a null
    value for DischargeDate and 0 if there isn't. I couldn't manage that. Thanks
    in advance,

    Buran


    buran Guest

  2. #2

    Default Re: how to determine whether a field has null value

    SELECT
    DischargeDate
    , flag = (case
    when DischargeDate is null then 1
    else 0
    end)
    FROM MedicalFollowUpInHospital
    WHERE OurFileNo = 'R03000003'

    --
    Dean Savovic
    www.teched.hr


    "buran" <com> wrote in message news:%phx.gbl... 


    Dean Guest

  3. #3

    Default Re: how to determine whether a field has null value

    select case when DischargeDate is null then 1 else 0 end as
    DischargeDateISNull

    from
    MedicalFollowUpInHospital



    "buran" <com> wrote in message
    news:%phx.gbl... 
    Thanks 


    Tammy Guest

  4. #4

    Default Re: how to determine whether a field has null value

    SELECT CASE
    WHEN DischargeDate IS NULL THEN 1
    ELSE 0
    END
    FROM dbo.MedicalFollowUpInHospital
    WHERE OurFileNo = 'R03000003'

    --
    Regards,

    Tomislav Kralj
    tel.hr

    "buran" <com> wrote in message
    news:#phx.gbl... 
    Thanks 


    Tomislav Guest

  5. #5

    Default Re: how to determine whether a field has null value

    Thanks, but how will I use this "flag"?

    Buran

    "Dean Savovic" <hr> wrote in message
    news:bfg7gf$r7q$net4u.hr... 
    news:%phx.gbl... [/ref]
    null [/ref]
    Thanks 
    >
    >[/ref]


    buran Guest

  6. #6

    Default Re: how to determine whether a field has null value

    Not sure I understand you!

    You wanted, qoute: "I would like to write a stored procedure whic returns 1 if there's a null
    value for DischargeDate and 0 if there isn't. I couldn't manage that. Thanks
    in advance..."

    Maybe you need this, but you where clause must return only one record:

    create procedure dbo.Proc
    (
    flag smallint = null output
    )
    as

    set nocount on

    SELECT
    flag = (case
    when DischargeDate is null then 1
    else 0
    end)
    FROM MedicalFollowUpInHospital
    WHERE OurFileNo = 'R03000003'

    return

    --
    Dean Savovic
    www.teched.hr


    "buran" <com> wrote in message news:ux2o%phx.gbl... 
    > news:%phx.gbl... [/ref]
    > null [/ref]
    > Thanks 
    > >
    > >[/ref]
    >
    >[/ref]


    Dean Guest

  7. #7

    Default Re: how to determine whether a field has null value

    C# example:

    int result = (int) sqlCommand.ExecuteScalar();

    Note that an exception will be thrown when you use ExecuteScalar and no
    rows are returned.

    --
    Hope this helps.

    Dan Guzman
    SQL Server MVP

    -----------------------
    SQL FAQ links (courtesy Neil Pike):

    http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
    http://www.sqlserverfaq.com
    http://www.mssqlserver.com/faq
    -----------------------

    "buran" <com> wrote in message
    news:phx.gbl... [/ref][/ref]
    a [/ref][/ref]
    that. 
    > >
    > >[/ref]
    >
    >[/ref]


    Dan Guest

Similar Threads

  1. Replies: 7
    Last Post: July 19th, 02:12 PM
  2. determine empty field
    By lingo_user in forum Macromedia ColdFusion
    Replies: 1
    Last Post: June 19th, 08:27 PM
  3. NULL To DateTime field through sp
    By cp_anil@rediff in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 15th, 09:10 AM
  4. Set a datetime field to Null
    By Baiju K U in forum ASP Database
    Replies: 3
    Last Post: October 15th, 12:36 PM
  5. Returns a NULL field ?
    By Chris Leonard in forum ASP Database
    Replies: 2
    Last Post: August 17th, 06:58 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139