Professional Web Applications Themes

CASE - Microsoft SQL / MS SQL Server

Hi, I have a stored procedure that I find a little bulky. I'm new to SQL programming, but from what I can see there must be a way to simplify the code I have written: -------------------------------- CREATE PROCEDURE STRDPROC_ACTIONLOG tpName VARCHAR(255), tblName VARCHAR(20), triggerAction INT AS IF triggerAction = 1 BEGIN INSERT tbl_ActionLog ( taxpayerName, userName, [dateTime], [add], tableName ) VALUES (tpName, USER, GETDATE(), 1, tblName) END IF triggerAction = 2 BEGIN INSERT tbl_ActionLog ( taxpayerName, userName, [dateTime], [update], tableName ) VALUES (tpName, USER, GETDATE(), 1, tblName) END IF triggerAction = 3 BEGIN INSERT tbl_ActionLog ( taxpayerName, userName, [dateTime], [delete], ...

  1. #1

    Default CASE

    Hi,

    I have a stored procedure that I find a little bulky. I'm
    new to SQL programming, but from what I can see there must
    be a way to simplify the code I have written:
    --------------------------------
    CREATE PROCEDURE STRDPROC_ACTIONLOG
    tpName VARCHAR(255),
    tblName VARCHAR(20),
    triggerAction INT
    AS
    IF triggerAction = 1
    BEGIN
    INSERT tbl_ActionLog ( taxpayerName,
    userName, [dateTime], [add], tableName )
    VALUES (tpName, USER, GETDATE(), 1,
    tblName)
    END
    IF triggerAction = 2
    BEGIN
    INSERT tbl_ActionLog ( taxpayerName,
    userName, [dateTime], [update], tableName )
    VALUES (tpName, USER, GETDATE(), 1,
    tblName)
    END
    IF triggerAction = 3
    BEGIN
    INSERT tbl_ActionLog ( taxpayerName,
    userName, [dateTime], [delete], tableName )
    VALUES (tpName, USER, GETDATE(), 1,
    tblName)
    END
    GO
    ---------------------------------------
    Now when I was writting this, the first thing i thought of
    that would eliminate code would have been a CASE
    statement. The only problem is, anything I tried was
    always incorrect. Can someone help me simplify this code.

    You'll notice that the only thing differenciating the
    threee if statements is the field [add], [update] and
    [delete]. I'm assuming there is a way to make it simpler.

    TIA,
    Eric
    Eric Guest

  2. #2

    Default Re: CASE

    You have a table design problem. Instead of having 3 columns - insert, update, delete - you should have one called, say, Type, and populate it with the appropriate value:

    CREATE PROCEDURE STRDPROC_ACTIONLOG
    tpName VARCHAR(255),
    tblName VARCHAR(20),
    triggerAction INT
    AS
    INSERT tbl_ActionLog ( taxpayerName,
    userName, [dateTime], [type], tableName )
    VALUES (tpName, USER, GETDATE(), triggerAction, tblName)
    GO


    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Eric" <com> wrote in message news:02bd01c3514b$97975420$gbl...
    Hi,

    I have a stored procedure that I find a little bulky. I'm
    new to SQL programming, but from what I can see there must
    be a way to simplify the code I have written:
    --------------------------------
    CREATE PROCEDURE STRDPROC_ACTIONLOG
    tpName VARCHAR(255),
    tblName VARCHAR(20),
    triggerAction INT
    AS
    IF triggerAction = 1
    BEGIN
    INSERT tbl_ActionLog ( taxpayerName,
    userName, [dateTime], [add], tableName )
    VALUES (tpName, USER, GETDATE(), 1,
    tblName)
    END
    IF triggerAction = 2
    BEGIN
    INSERT tbl_ActionLog ( taxpayerName,
    userName, [dateTime], [update], tableName )
    VALUES (tpName, USER, GETDATE(), 1,
    tblName)
    END
    IF triggerAction = 3
    BEGIN
    INSERT tbl_ActionLog ( taxpayerName,
    userName, [dateTime], [delete], tableName )
    VALUES (tpName, USER, GETDATE(), 1,
    tblName)
    END
    GO
    ---------------------------------------
    Now when I was writting this, the first thing i thought of
    that would eliminate code would have been a CASE
    statement. The only problem is, anything I tried was
    always incorrect. Can someone help me simplify this code.

    You'll notice that the only thing differenciating the
    threee if statements is the field [add], [update] and
    [delete]. I'm assuming there is a way to make it simpler.

    TIA,
    Eric

    Tom Guest

  3. #3

    Default Re: CASE

    Hi,

    Your right, it doesn't look like there is any difference,
    but look at the fourth column in the INSRT statement. The
    first IF has [add] the second has [update] and the third
    has [delete]. According to the integer I pass, I want that
    field to be change according to that variable.

    Regards,
    Eric

     
    when triggerAction [/ref]
    I'm [/ref]
    must [/ref]
    of [/ref]
    code. [/ref]
    simpler. 
    >
    >
    >.
    >[/ref]
    Eric Guest

  4. #4

    Default Re: CASE

    Ah, I see, you have a column named add, a column named update, and a column
    named delete. I agree with Tom, your table design needs work.

    You can add a view so that a human can read the triggerAction as a word
    instead of an int,

    CREATE VIEW vActionLog
    AS
    SELECT taxpayerName,
    username,
    [datetime],
    type = CASE type
    WHEN 1 THEN 'INSERT'
    WHEN 2 THEN 'UPDATE'
    WHEN 3 THEN 'DELETE'
    END,
    tablename
    FROM tbl_ActionLog


    Aaron Guest

  5. #5

    Default Re: CASE

    LOL! ;-)

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Aaron Bertrand - MVP" <com> wrote in message news:phx.gbl... [/ref]

    Must be rare. :-P



    Tom Guest

Similar Threads

  1. Replies: 112
    Last Post: December 9th, 05:46 PM
  2. Is there a way to convert lower case text to upper case text in PHP?
    By tanas@ing.com.au in forum PHP Development
    Replies: 3
    Last Post: December 11th, 06:12 AM
  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

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