Professional Web Applications Themes

Using conditional logic in a select statemet (I.e IFs) - Microsoft SQL / MS SQL Server

The (more poweful) equivalent of IIF in Transact-SQL is CASE. You can find extensive inforamtion about the syntax in SQL Server books Online, but here is a simple example with the code you provided: SELECT Customer, CASE WHEN LEFT(Status, 5) = 'Empty' THEN 'Empty' ELSE 'Loaded' END AS Ldd_Status FROM dbo.STOCK -- Jacco Schalkwijk MCDBA, MCSD, MCSE Database Administrator Eurostop Ltd. "Chris Strug" <hotmailsolace1884.com> wrote in message news:eXPE#LUQDHA.3236TK2MSFTNGP10.phx.gbl... > Hi, > > I'm something of a newbie to SQL Server and was wondering if someone could > help me with this. > > Basically, I would like to include a ...

  1. #1

    Default Re: Using conditional logic in a select statemet (I.e IFs)

    The (more poweful) equivalent of IIF in Transact-SQL is CASE. You can find
    extensive inforamtion about the syntax in SQL Server books Online, but here
    is a simple example with the code you provided:

    SELECT Customer,
    CASE WHEN LEFT(Status, 5) = 'Empty' THEN 'Empty'
    ELSE 'Loaded' END AS Ldd_Status
    FROM dbo.STOCK

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Chris Strug" <hotmailsolace1884.com> wrote in message
    news:eXPE#LUQDHA.3236TK2MSFTNGP10.phx.gbl...
    > Hi,
    >
    > I'm something of a newbie to SQL Server and was wondering if someone could
    > help me with this.
    >
    > Basically, I would like to include a construct statement that includes an
    > IF. Specifically, I have a field named status. This can be one of several
    > values but basically boils down to either 'Empty' or 'Loaded'. The way to
    > determine is that if the first 5 characters of the field are 'Empty' then
    it
    > is empty otherwise it is 'Loaded'.
    >
    > How do I create this functionality in a select statement.
    >
    > I have the following:
    >
    > SELECT Customer, LEFT(Status, 5) AS Ldd_Status
    > FROM dbo.STOCK
    >
    > I've tried using iif and IF...ELSE functions but either my syntax is wrong
    > or I'm using them in the wrong way somehow.
    >
    > If anyone could explain how to include a logical comparison as I've
    > requested that would be great.
    >
    > Any help gratefully received.
    >
    > Kind thanks
    >
    > Chris Strug
    >
    >

    Jacco Schalkwijk Guest

  2. #2

    Default Re: Using conditional logic in a select statemet (I.e IFs)

    You can use the CASE statement which functions like the VB CASE or even
    multiple IF's. Here is a basic syntax.
    SELECT colA,
    CASE [expression]
    WHEN [value] THEN ...
    WHEN [value] THEN ...
    ELSE ...
    END As colB
    FROM
    yourTable
    WHERE
    ...
    There is also another form of the CASE statement (the example below uses
    this). So, for your case, your would write:
    SELECT
    CASE
    WHEN LEFT(Status, 5) = 'Empty' THEN 'Empty' ELSE 'Loaded'
    END
    FROM ...
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    [url]http://www32.brinkster.com/srisamp[/url]

    "Chris Strug" <hotmailsolace1884.com> wrote in message
    news:eXPE%23LUQDHA.3236TK2MSFTNGP10.phx.gbl...
    > Hi,
    >
    > I'm something of a newbie to SQL Server and was wondering if someone could
    > help me with this.
    >
    > Basically, I would like to include a construct statement that includes an
    > IF. Specifically, I have a field named status. This can be one of several
    > values but basically boils down to either 'Empty' or 'Loaded'. The way to
    > determine is that if the first 5 characters of the field are 'Empty' then
    it
    > is empty otherwise it is 'Loaded'.
    >
    > How do I create this functionality in a select statement.
    >
    > I have the following:
    >
    > SELECT Customer, LEFT(Status, 5) AS Ldd_Status
    > FROM dbo.STOCK
    >
    > I've tried using iif and IF...ELSE functions but either my syntax is wrong
    > or I'm using them in the wrong way somehow.
    >
    > If anyone could explain how to include a logical comparison as I've
    > requested that would be great.
    >
    > Any help gratefully received.
    >
    > Kind thanks
    >
    > Chris Strug
    >
    >

    SriSamp Guest

Similar Threads

  1. Logic
    By anonymous in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: March 30th, 02:40 PM
  2. Replies: 0
    Last Post: September 24th, 03:24 AM
  3. Replies: 0
    Last Post: September 11th, 11:26 AM
  4. Replies: 0
    Last Post: September 11th, 12:19 AM
  5. Replies: 0
    Last Post: April 15th, 01:22 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