Professional Web Applications Themes

First Time Trans Error Control -- Help Please - Microsoft SQL / MS SQL Server

I have coded the following in a stored procedure. --------------------------------------------------------------START CREATE PROCEDURE dbo.sp_ChangeOEReqShipDate ordno CHAR(5), newdate INT AS BEGIN TRAN SELECT * FROM OEORDHDR_SQL WHERE ord_no = '000' + ordno IF ROWCOUNT = 0 GOTO NoSuchOrder IF newdate < 20030701 OR newdate > 20040630 GOTO DateBad UPDATE OEORDLIN_SQL SET req_ship_dt = newdate WHERE (ord_no = '000' + ordno) IF error <> 0 GOTO CannotChangeDate COMMIT TRAN NoSuchOrder: PRINT CHAR (10) + 'There is no such live order in the system. Check the number and try again. ' + CHAR(10) ROLLBACK TRAN DateBad: PRINT CHAR (10) + 'The date is not ...

  1. #1

    Default First Time Trans Error Control -- Help Please

    I have coded the following in a stored procedure.

    --------------------------------------------------------------START
    CREATE PROCEDURE dbo.sp_ChangeOEReqShipDate
    ordno CHAR(5), newdate INT

    AS

    BEGIN TRAN

    SELECT *
    FROM OEORDHDR_SQL
    WHERE ord_no = '000' + ordno

    IF ROWCOUNT = 0
    GOTO NoSuchOrder

    IF newdate < 20030701 OR newdate > 20040630
    GOTO DateBad

    UPDATE OEORDLIN_SQL
    SET req_ship_dt = newdate
    WHERE (ord_no = '000' + ordno)

    IF error <> 0
    GOTO CannotChangeDate

    COMMIT TRAN

    NoSuchOrder:

    PRINT CHAR (10)
    + 'There is no such live order in the system. Check the number and try
    again. '
    + CHAR(10)

    ROLLBACK TRAN

    DateBad:

    PRINT CHAR (10)
    + 'The date is not within the current fiscal year. Check the date and try
    again. '
    + CHAR(10)

    ROLLBACK TRAN

    CannotChangeDate:

    SELECT error

    PRINT CHAR (10)
    + 'Please print this screen and report to administrator. '
    + CHAR(10)

    ROLLBACK TRAN
    GO
    --------------------------------------------------------END

    I get the following errors when I enter ordno = 56517 and newdate =
    20030712 (our system uses integers for dates -- I don't know why):

    --------------------------------------------------------START
    ord_type ord_no status entered_dt ord_dt apply_to_no oe_po_no
    cus_no bal_meth bill_to_name
    bill_to_addr_1 bill_to_addr_2
    bill_to_addr_3 bill_to_country cus_alt_adr_cd
    ship_to_name ship_to_addr_1
    ship_to_addr_2 ship_to_addr_3
    ship_to_country shipping_dt ship_via_cd ar_terms_cd frt_pay_cd
    ship_instruction_1 ship_instruction_2
    slspsn_no slspsn_pct_comm slspsn_comm_amt slspsn_no_2 slspsn_pct_comm_2
    slspsn_comm_amt_2 slspsn_no_3 slspsn_pct_comm_3 slspsn_comm_amt_3 tax_cd
    tax_pct tax_cd_2 tax_pct_2 tax_cd_3 tax_pct_3 discount_pct job_no
    mfg_loc profit_center dept ar_reference tot_sls_amt
    tot_sls_disc tot_tax_amt tot_cost tot_weight misc_amt
    misc_mn_no misc_sb_no misc_dp_no frt_amt frt_mn_no frt_sb_no
    frt_dp_no sls_tax_amt_1 sls_tax_amt_2 sls_tax_amt_3 comm_pct
    comm_amt cmt_1 cmt_2
    cmt_3 payment_amt payment_disc_amt chk_no
    chk_dt cash_mn_no cash_sb_no cash_dp_no picked_dt billed_dt inv_no
    inv_dt selection_cd posted_dt part_posted_fg ship_to_freefrm_fg
    bill_to_freefrm_fg copy_to_bm_fg edi_fg closed_fg ac_misc_amt
    ac_frt_amt ac_tot_tax_amt ac_sls_tax_amt ac_tot_sls_amt
    hold_fg prepayment_fg lost_sale_cd orig_ord_type orig_ord_dt orig_ord_no
    award_probability oe_cash_no exch_rt_fg curr_cd orig_trx_rt curr_trx_rt
    tax_sched user_def_fld_1 user_def_fld_2
    user_def_fld_3 user_def_fld_4 user_def_fld_5
    deter_rate_by form_no tax_fg sls_mn_no sls_sb_no sls_dp_no shipped_dt
    tot_dollars mult_loc_fg tot_tax_cost hist_load_record
    pre_select_status packing_no deliv_ar_terms_cd inv_batch_id rma_no
    filler_0001
    A4GLIdentity
    -------- -------- ------ ----------- ----------- ----------- ---------------
    ---------- ------------ -------- ---------------------------------------- --
    -------------------------------------- -------------------------------------
    --- ---------------------------------------- -------------------- ----------
    ----- ---------------------------------------- -----------------------------
    ----------- ---------------------------------------- -----------------------
    ----------------- -------------------- ----------- ----------- ----------- -
    --------- ---------------------------------------- -------------------------
    --------------- --------- --------------- ---------------- ----------- -----
    ------------ ----------------- ----------- ----------------- ---------------
    -- ------ -------- -------- --------- -------- --------- ------------ ------
    ------ ------- ------------- -------- ------------------------------ -------
    --------- ---------------- ---------------- ---------------- ------------ --
    -------------- ---------- ---------- ---------- ---------------- --------- -
    -------- --------- ---------------- ---------------- ---------------- ------
    -- ---------------- ----------------------------------- --------------------
    --------------- ----------------------------------- ---------------- -------
    --------- ----------- ----------- ---------- ---------- ---------- ---------
    -- ----------- ----------- ----------- ------------ ----------- ------------
    -- ------------------ ------------------ ------------- ------ --------- ----
    ------------ ---------------- ----------------- ----------------- ----------
    ------- ------- ------------- ------------ ------------- ----------- -------
    ---- ----------------- ----------- ---------- ------- ------------- --------
    ----- --------- ------------------------------ -----------------------------
    - ------------------------------ ------------------------------ ------------
    ------------------ ------------- ------- ------ --------- --------- --------
    - ----------- ---------------- ----------- ---------------- ----------------
    ----------------- ----------- ----------------- ------------ ----------- --
    ----------------------------------------------------------------------------
    ----------------------------------- ------------
    O 00056517 4 20030707 20030707 0 ERNIE
    000000030067 O Homemaid Ravioli 109 South
    Boulevard NULL San
    Mateo, CA 94402 USA SAN MATEO MAIN
    Homemaid Ravioli 109 South Boulevard
    NULL San Mateo, CA 94402
    USA 20030714 DEL N13 NULL NULL
    NULL MGP 100.000 .00
    NULL .000 .00 NULL .000
    ..00 NON .0000 NULL .0000 NULL .0000 .00
    NULL AC NULL NULL NULL
    1070.91 .00 .00 875.76 1980.500
    ..00 NULL NULL NULL .00 NULL
    NULL NULL .00 .00 .00 .00
    ..00 NULL NULL
    sql check .00 .00 0
    0 NULL NULL NULL 0 0 0
    0 C 0 NULL NULL NULL
    NULL NULL NULL .00 .00 .00
    ..00 .00 NULL N NULL NULL
    0 00000000 0 0 NULL
    1.000000 1.000000 NULL NULL NULL
    NULL NULL NULL
    NULL 50 N NULL NULL NULL 0
    1070.91 NULL .00 NULL 1
    0 NULL NULL 0 NULL
    7952

    (1 row(s) affected)


    (4 row(s) affected)


    There is no such live order in the system. Check the number and try again.

    Server: Msg 3903, Level 16, State 1, Line 34
    The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

    The date is not within the current fiscal year. Check the date and try
    again.

    Server: Msg 3903, Level 16, State 1, Line 42
    The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

    Please print this screen and report to administrator.

    Server: Msg 3903, Level 16, State 1, Line 50
    The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
    ----------------------------------------------------------------------------
    --------------------------------------END

    The sp properly updates the req_ship_dt field, but I get the preceding
    errors.

    How do I properly trapped the errors without always printing them?
    How do I check for the existence of an order without SELECT which the user
    should not have to see?

    Thanks in advance.


    --
    Mark Simmerman
    SQL Learner
    Napa, CA, USA


    Mark Simmerman Guest

  2. #2

    Default Re: First Time Trans Error Control -- Help Please

    (inline)
    "Mark Simmerman" <postmastermezzetta.com> wrote in message
    news:%233RjffLRDHA.3796tk2msftngp13.phx.gbl...
    > I have coded the following in a stored procedure.
    You were falling through into your labeled error handlers, but I still
    didn't like the SP's structure.

    Here's how I would have coded it:




    CREATE PROCEDURE sp_ChangeOEReqShipDate ordno CHAR(5), newdate INT
    AS
    --BEGIN TRAN
    --don't bother with a transaction here
    --you're only running a single update
    --Single DML statements are always atomic


    --don't return these rows to the client
    --instead select into a variable and check it
    declare ord_no varchar(50)

    SELECT ord_no = ord_no
    FROM OEORDHDR_SQL
    WHERE ord_no = '000' + ordno

    IF ord_no is null
    begin
    --just raise the error and jump to your error handler label
    raiserror( 'There is no such live order in the system.
    Check the number and try again. ',16,1)
    goto eh
    end

    IF newdate < 20030701 OR newdate > 20040630
    begin
    raiserror( 'The date is not within the current fiscal year.
    Check the date and try again.',16,1)
    goto eh
    end

    UPDATE OEORDLIN_SQL
    SET req_ship_dt = newdate
    WHERE (ord_no = '000' + ordno)

    IF error <> 0
    GOTO eh

    -- COMMIT TRAN
    return 0 --you were falling through into your labeled blocks

    eh:
    --rollback tran
    return 1

    --if you always raise errors (or check if SQL did) then you only
    --need one label for your error handler

    David



    David Browne Guest

  3. #3

    Default Re: First Time Trans Error Control -- Help Please

    [posted and mailed, please reply in public]

    Mark Simmerman (postmastermezzetta.com) writes:
    > CREATE PROCEDURE dbo.sp_ChangeOEReqShipDate
    Don't name your procedures sp_something. The sp_ prefix is reserved for
    system procedures, and SQL Server first looks in the master databaes for
    these.
    > UPDATE OEORDLIN_SQL
    > SET req_ship_dt = newdate
    > WHERE (ord_no = '000' + ordno)
    >
    > IF error <> 0
    > GOTO CannotChangeDate
    Always save error into local variable, since error is set after each
    statement. You have a SELECT of error further down. At that point error
    is always 0.
    > COMMIT TRAN
    >
    > NoSuchOrder:
    You should have a RETURN after the COMMIT as you should have after each
    ROLLBACK - now you are executing your error handlers too.
    > PRINT CHAR (10)
    > + 'There is no such live order in the system. Check the number and try
    > again. '
    > + CHAR(10)
    Better to use RAISERROR.
    > How do I properly trapped the errors without always printing them?
    As David Browne noted, get out the procedure after the COMMIT/ROLLBACK.
    > How do I check for the existence of an order without SELECT which the user
    > should not have to see?
    IF EXISTS (SELECT ....)


    --
    Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]

    Books Online for SQL Server SP3 at
    [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]
    Erland Sommarskog Guest

  4. #4

    Default Re: First Time Trans Error Control -- Help Please

    David,

    Thanks for the help. SQL Server Programming By Example (Que publishing)
    pages 571-3 completely omitted the RETURN() statements. I guess cookbook
    programming will only get you so far...

    Your suggestions on using RAISEERROR are well taken. I could not get
    any variation of your 'SELECT ord_no = ord_no' suggestion to work, so I
    used a IF NOT EXISTS (SELECT...).

    I will also search for a better SQL programming book.

    Mark



    "David Browne" <davidbaxterbrowne no potted [email]meathotmail.com[/email]> wrote in
    message news:egEejJMRDHA.2432TK2MSFTNGP10.phx.gbl...
    > (inline)
    > "Mark Simmerman" <postmastermezzetta.com> wrote in message
    > news:%233RjffLRDHA.3796tk2msftngp13.phx.gbl...
    > > I have coded the following in a stored procedure.
    >
    > You were falling through into your labeled error handlers, but I still
    > didn't like the SP's structure.
    >
    > Here's how I would have coded it:
    >
    >
    >
    >
    > CREATE PROCEDURE sp_ChangeOEReqShipDate ordno CHAR(5), newdate INT
    > AS
    > --BEGIN TRAN
    > --don't bother with a transaction here
    > --you're only running a single update
    > --Single DML statements are always atomic
    >
    >
    > --don't return these rows to the client
    > --instead select into a variable and check it
    > declare ord_no varchar(50)
    >
    > SELECT ord_no = ord_no
    > FROM OEORDHDR_SQL
    > WHERE ord_no = '000' + ordno
    >
    > IF ord_no is null
    > begin
    > --just raise the error and jump to your error handler label
    > raiserror( 'There is no such live order in the system.
    > Check the number and try again. ',16,1)
    > goto eh
    > end
    >
    > IF newdate < 20030701 OR newdate > 20040630
    > begin
    > raiserror( 'The date is not within the current fiscal year.
    > Check the date and try again.',16,1)
    > goto eh
    > end
    >
    > UPDATE OEORDLIN_SQL
    > SET req_ship_dt = newdate
    > WHERE (ord_no = '000' + ordno)
    >
    > IF error <> 0
    > GOTO eh
    >
    > -- COMMIT TRAN
    > return 0 --you were falling through into your labeled blocks
    >
    > eh:
    > --rollback tran
    > return 1
    >
    > --if you always raise errors (or check if SQL did) then you only
    > --need one label for your error handler
    >
    > David
    >
    >
    >

    Mark Simmerman Guest

  5. #5

    Default Re: First Time Trans Error Control -- Help Please

    Erland,

    Thanks for the help. I used your suggestion of the IF NOT EXISTS(SELECT
    ....). I will practice the RAISEERROR. I will also look for a good book on
    SQL Programming.

    Mark

    "Erland Sommarskog" <sommaralgonet.se> wrote in message
    news:Xns93B275F238F9Yazorman127.0.0.1...
    > [posted and mailed, please reply in public]
    >
    > Mark Simmerman (postmastermezzetta.com) writes:
    > > CREATE PROCEDURE dbo.sp_ChangeOEReqShipDate
    >
    > Don't name your procedures sp_something. The sp_ prefix is reserved for
    > system procedures, and SQL Server first looks in the master databaes for
    > these.
    >
    > > UPDATE OEORDLIN_SQL
    > > SET req_ship_dt = newdate
    > > WHERE (ord_no = '000' + ordno)
    > >
    > > IF error <> 0
    > > GOTO CannotChangeDate
    >
    > Always save error into local variable, since error is set after each
    > statement. You have a SELECT of error further down. At that point
    error
    > is always 0.
    >
    > > COMMIT TRAN
    > >
    > > NoSuchOrder:
    >
    > You should have a RETURN after the COMMIT as you should have after each
    > ROLLBACK - now you are executing your error handlers too.
    >
    > > PRINT CHAR (10)
    > > + 'There is no such live order in the system. Check the number and try
    > > again. '
    > > + CHAR(10)
    >
    > Better to use RAISERROR.
    >
    > > How do I properly trapped the errors without always printing them?
    >
    > As David Browne noted, get out the procedure after the COMMIT/ROLLBACK.
    >
    > > How do I check for the existence of an order without SELECT which the
    user
    > > should not have to see?
    >
    > IF EXISTS (SELECT ....)
    >
    >
    > --
    > Erland Sommarskog, SQL Server MVP, [email]sommaralgonet.se[/email]
    >
    > Books Online for SQL Server SP3 at
    > [url]http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp[/url]

    Mark Simmerman Guest

Similar Threads

  1. Trans slide show
    By r s sheen in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: September 1st, 05:13 PM
  2. Child control renders at desing-time as if it is in run-time...
    By Zarko Gajic in forum ASP.NET Building Controls
    Replies: 1
    Last Post: March 16th, 07:54 AM
  3. Replies: 0
    Last Post: August 2nd, 10:43 AM
  4. Error pop-up every time Control panel is started.
    By Sushe Sama in forum Windows Setup, Administration & Security
    Replies: 1
    Last Post: July 12th, 11:28 PM
  5. Replies: 2
    Last Post: July 2nd, 08:05 AM

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