Professional Web Applications Themes

Case Syntax Problem - Microsoft SQL / MS SQL Server

This sproc was fine until i added the Case When part. I've always used if then and just wanted to learn to use Case When, but something is wrong with my syntax. Can anyone tell me what's wrong with the Case When part? CREATE PROCEDURE GetDowntime ( sPlant Int = Null, sShiftID Int = Null, sTeamID Int = Null, sDataType Int ) AS BEGIN SET NOCOUNT ON IF sTeamID Is Null BEGIN IF sPlant = '1' BEGIN SELECT * FROM tblDowntime END IF sPlant = '2' BEGIN SELECT * FROM tblDowntime END END ELSE BEGIN IF sPlant = '1' BEGIN ...

  1. #1

    Default Case Syntax Problem

    This sproc was fine until i added the Case When part. I've always used if
    then and just wanted to learn to use Case When, but something is wrong with
    my syntax.

    Can anyone tell me what's wrong with the Case When part?

    CREATE PROCEDURE GetDowntime
    (
    sPlant Int = Null,
    sShiftID Int = Null,
    sTeamID Int = Null,
    sDataType Int
    )
    AS
    BEGIN
    SET NOCOUNT ON
    IF sTeamID Is Null
    BEGIN
    IF sPlant = '1'
    BEGIN
    SELECT * FROM tblDowntime
    END

    IF sPlant = '2'
    BEGIN
    SELECT * FROM tblDowntime
    END
    END

    ELSE
    BEGIN
    IF sPlant = '1'
    BEGIN

    CASE When sDataType = '1' THEN

    SELECT * tblDowntime

    Else
    SELECT * tblDowntime


    END


    END

    IF sPlant = '2'
    BEGIN
    SELECT * FROM tblDowntime
    END
    END
    END
    GO


    Scott Guest

  2. #2

    Default Re: Case Syntax Problem

    stupid question:

    how do i find books online? i install everything, but when i search help,
    almost no topics exist.

    "John Bell" <com> wrote in message
    news:3f353a79$0$18256$easynet.co.uk... [/ref]
    if 
    > with 
    >
    >[/ref]


    Scott Guest

  3. #3

    Default Re: Case Syntax Problem

    Unlike IF, CASE is not a control-of-flow statement. CASE returns an
    expression. For example:

    SELECT
    ProductName,
    CASE
    WHEN UnitsInStock > 0 THEN 'In Stock'
    ELSE 'Out of Stock' END
    FROM Northwind..Products

    --
    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
    -----------------------

    "Scott" <com> wrote in message
    news:uRhmT$phx.gbl... 
    if 
    with 


    Dan Guest

  4. #4

    Default Re: Case Syntax Problem

    Hi

    CASE is s function, you can use it like:

    SELECT CASE WHEN sPlant = '1' THEN Col1
    ELSE Col2
    END
    FROM tblDowntime

    OR

    SELECT CASE sPlant WHEN '1' THEN Col1
    ELSE Col2
    END
    FROM tblDowntime

    See Books Online for more details and examples.

    John

    "Scott" <com> wrote in message
    news:uRhmT$phx.gbl... 
    with 


    John Guest

  5. #5

    Default Re: Case Syntax Problem

    Look in your SQL Server program group in the Windows Start Menu. It should
    be in there as SQL Books Online.

    Scott wrote: [/ref][/ref]



    Bob Guest

Similar Threads

  1. Replies: 112
    Last Post: December 9th, 05:46 PM
  2. Replies: 22
    Last Post: February 5th, 12:03 PM
  3. #23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
    By nvivo at mandic dot com dot br in forum PHP Development
    Replies: 0
    Last Post: October 19th, 12:17 PM
  4. case conversion problem
    By James Edward Gray II in forum PERL Beginners
    Replies: 7
    Last Post: September 11th, 07:34 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