Professional Web Applications Themes

IF, THEN, ELSE statements - Microsoft SQL / MS SQL Server

Hi, Need help again. Can anyone give me advise on IF, THEN, ELSE statements from SYBASE to MSSQL. I am at the business end of finishing this project and got stuck with a few stored procedures...see below...the if then else statement is where I'm up to. alter procedure "DBA".SP_PR_ORDER_READRECORDER(ORDERNUMBER integer) as begin execute sp_security_checkPerms 'read','order',orderNumber select TORDER.ORDERNUMBER, TORDER.COMPANYCODE, TORDER.ORDEREDBY, TORDER.TAKENBY, TORDER.DIVISIONID, TORDER.TAKENDATE, TORDER.REASONCODE, TORDER.COORDINATOR, TORDER.TAKENTIME, TORDER.CREATEBY, TORDER.CREATEDATE, (if TORDER.LASTMODDATE>TRECRUITMENTORDER.LASTMODDATE then TORDER.LASTMODBY else TRECRUITMENTORDER.LASTMODBY endif), (if TORDER.LASTMODDATE>TRECRUITMENTORDER.LASTMODDATE then TORDER.LASTMODDATE else TRECRUITMENTORDER.LASTMODDATE endif), TORDER.STARTDATE, TORDER.STARTTIME, TORDER.ORDERCONTACT, TORDER.DATECONFIRMED, TORDER.STATUSEFFECTIVEDATE, TORDER.ORDERSTATUS, TORDER.CUSTOMERORDERCODE, tOrder.OrderSource, TORDER.DELETED, TRECRUITMENTORDER.JOBTITLE, TRECRUITMENTORDER.DEFAULTPOSITIONCODE, TRECRUITMENTORDER.DEFAULTCATEGORY, TRECRUITMENTORDER.ENDDATE, TRECRUITMENTORDER.ENDTIME, TRECRUITMENTORDER.LUNCHEND, TRECRUITMENTORDER.LUNCHSTART, TRECRUITMENTORDER.DATEPOSTEDTOWEB, TRECRUITMENTORDER.DATEREMOVEDFROMWEB, TRECRUITMENTORDER.ADVERTISEONWEB, ...

  1. #1

    Default IF, THEN, ELSE statements

    Hi,

    Need help again. Can anyone give me advise on IF, THEN,
    ELSE statements from SYBASE to MSSQL. I am at the business
    end of finishing this project and got stuck with a few
    stored procedures...see below...the if then else statement
    is where I'm up to.


    alter procedure
    "DBA".SP_PR_ORDER_READRECORDER(ORDERNUMBER integer)
    as
    begin
    execute
    sp_security_checkPerms 'read','order',orderNumber
    select TORDER.ORDERNUMBER,
    TORDER.COMPANYCODE,
    TORDER.ORDEREDBY,
    TORDER.TAKENBY,
    TORDER.DIVISIONID,
    TORDER.TAKENDATE,
    TORDER.REASONCODE,
    TORDER.COORDINATOR,
    TORDER.TAKENTIME,
    TORDER.CREATEBY,
    TORDER.CREATEDATE,
    (if TORDER.LASTMODDATE>TRECRUITMENTORDER.LASTMODDATE
    then TORDER.LASTMODBY else TRECRUITMENTORDER.LASTMODBY
    endif),
    (if TORDER.LASTMODDATE>TRECRUITMENTORDER.LASTMODDATE
    then TORDER.LASTMODDATE else TRECRUITMENTORDER.LASTMODDATE
    endif),
    TORDER.STARTDATE,
    TORDER.STARTTIME,
    TORDER.ORDERCONTACT,
    TORDER.DATECONFIRMED,
    TORDER.STATUSEFFECTIVEDATE,
    TORDER.ORDERSTATUS,
    TORDER.CUSTOMERORDERCODE,
    tOrder.OrderSource,
    TORDER.DELETED,
    TRECRUITMENTORDER.JOBTITLE,
    TRECRUITMENTORDER.DEFAULTPOSITIONCODE,
    TRECRUITMENTORDER.DEFAULTCATEGORY,
    TRECRUITMENTORDER.ENDDATE,
    TRECRUITMENTORDER.ENDTIME,
    TRECRUITMENTORDER.LUNCHEND,
    TRECRUITMENTORDER.LUNCHSTART,
    TRECRUITMENTORDER.DATEPOSTEDTOWEB,
    TRECRUITMENTORDER.DATEREMOVEDFROMWEB,
    TRECRUITMENTORDER.ADVERTISEONWEB,
    TRECRUITMENTORDER.REPORTINGTOCONTACT,
    TRECRUITMENTORDER.REPORTINGTOTEXT,
    TRECRUITMENTORDER.REPORTINGTOFAX,
    TRECRUITMENTORDER.REPORTINGTOPHONE,
    TRECRUITMENTORDER.DAYSINCYCLE,
    TRECRUITMENTORDER.WORKWEEKENDFLAG,

    TRECRUITMENTORDER.STARTDAYNUMBERINCYCLE,f_getEntit yDescript
    ion('order',
    TRECRUITMENTORDER.ORDERNUMBER),f_getEntityDescript ion
    ('person',
    (select PERSONCODE
    from TCOMPANYCONTACT
    where
    CONTACTCODE=TORDER.ORDEREDBY)),f_getEntityDescript ion
    ('person',
    (select PERSONCODE
    from TCOMPANYCONTACT
    where
    CONTACTCODE=TORDER.ORDERCONTACT)),f_getEntityDescr iption
    ('usr',
    TORDER.COORDINATOR),f_getEntityDescription('usr',
    TORDER.TAKENBY),f_getEntityDescription('division',
    TORDER.DIVISIONID),f_getEntityDescription('company ',
    TORDER.COMPANYCODE),f_getEntityDescription('usr',
    TORDER.CREATEBY),f_getEntityDescription('usr',
    TORDER.LASTMODBY),f_getEntityDescription('person',
    (select PERSONCODE
    from TCOMPANYCONTACT
    where
    CONTACTCODE=TRECRUITMENTORDER.REPORTINGTOCONTACT)) ,
    (select POSITIONDESCRIPTION
    from TPOSITION
    where
    POSITIONCODE=TRECRUITMENTORDER.DEFAULTPOSITIONCODE ),
    TORDER.LONGNOTE,
    TRECRUITMENTORDER.LONGDESCRIPTION,
    TRECRUITMENTORDER.WEBPAGEDESCRIPTION
    from TORDER join TRECRUITMENTORDER on
    TORDER.ORDERNUMBER=TRECRUITMENTORDER.ORDERNUMBER
    where TORDER.ORDERNUMBER=ORDERNUMBER
    end
    Jeremy Guest

  2. #2

    Default Re: IF, THEN, ELSE statements

    Look up the case statement in BOL.

    Simple CASE function:

    CASE input_expression
    WHEN when_expression THEN result_expression
    [ ...n ]
    [
    ELSE else_result_expression
    ]
    END

    Searched CASE function:

    CASE
    WHEN Boolean_expression THEN result_expression
    [ ...n ]
    [
    ELSE else_result_expression
    ]
    END

    Tim S


    "Jeremy" <com.au> wrote in message
    news:070801c34a6e$c4962ca0$gbl... 



    Tim Guest

  3. #3

    Default Re: IF, THEN, ELSE statements

    Hi Guys,

    thanks for your reply. Being kind of new at all this. has
    given me a small hurdle. Nevertheless, I have tried the
    case statement before but didn't get it working. However,
    I'll try again...

    Thanks for your help.

    Jeremy [/ref]
    business [/ref]
    statement [/ref]
    TRECRUITMENTORDER.LASTMODDATE [/ref]
    TRECRUITMENTORDER.STARTDAYNUMBERINCYCLE,f_getEntit yDescript [/ref]
    TRECRUITMENTORDER.ORDERNUMBER),f_getEntityDescript ion 
    >
    >
    >
    >.
    >[/ref]
    Jeremy Guest

  4. #4

    Default Re: IF, THEN, ELSE statements

    >> Nevertheless, I have tried the case statement before but didn't get
    it working. <<

    Watch out -- it is a CASE **expression**, not a CASE **statement**.
    Statements control flow and execution; expressions return a value of ONE
    datatype.

    Watch the syntax because it has two formats.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. If Statements????
    By ccarterca in forum Macromedia Dynamic HTML
    Replies: 0
    Last Post: October 17th, 12:34 PM
  2. If statements in PHP?
    By barbedwire103 in forum Dreamweaver AppDev
    Replies: 1
    Last Post: April 11th, 11:24 AM
  3. Include statements
    By The Voivod in forum PHP Development
    Replies: 1
    Last Post: August 5th, 06:40 AM
  4. echo statements
    By Jay Fitzgerald in forum PHP Development
    Replies: 2
    Last Post: July 19th, 12:36 AM
  5. If/else statements - help.
    By Chris in forum PHP Development
    Replies: 1
    Last Post: June 28th, 01:55 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