Professional Web Applications Themes

Check in a Stored Procedure What violations have been done - Microsoft SQL / MS SQL Server

Hi Harry, Personally (and I know people are going to disagree with me) I wouldn't check separately for the existence of a country at all, a foreign key can handle that perfectly well. As you pointed out, the only time the problem can occur in your application is when there is a new installation and it isn't set up properly, so a proper instalaltiona nd testing procedure should take care of it. Why do you not provide a table with all the countries in the world with in your database, there are only 220 or so of them, so you ...

  1. #1

    Default Re: Check in a Stored Procedure What violations have been done

    Hi Harry,

    Personally (and I know people are going to disagree with me) I wouldn't
    check separately for the existence of a country at all, a foreign key can
    handle that perfectly well. As you pointed out, the only time the problem
    can occur in your application is when there is a new installation and it
    isn't set up properly, so a proper instalaltiona nd testing procedure should
    take care of it. Why do you not provide a table with all the countries in
    the world with in your database, there are only 220 or so of them, so you
    won't have to set them up separately?

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


    "Harry Leboeuf" <moc.silopenikfueobelh> wrote in message
    news:phx.gbl... 
    But 
    Insert 
    data 
    16, 
    must 
    violation 


    Jacco Guest

  2. #2

    Default Re: Check in a Stored Procedure What violations have been done

    Ok, Country was just an example, in this procedure i'm checking 5
    referential keys before my insert. Country 'never' changes (or a new one)
    but others might change, in our business (movie theatres) we have up to 5
    new features a week coming out. These will each be inserted once, but every
    day we have over 50000 transactions on them, now i'm checking them every
    time.
    No, i could not be sure that i'll create them 'manually' before posting the
    transactions. These transaction are coming from several systems, some even
    are DBASE III app's. They just 'post' a file to us.
    Ok, i could first go over all the records, check out how many distinct
    key1's there are, check them all, then the same for key2's and so on. Once
    all keys are verified then insert the whole bunch of transactions, but there
    must be a better way. I was doing this kind of processing in PL/SQL and it
    was working by checking on the key violation. (Key's had fixed names).

    Thx

    (Sorry, could not reply to your message directly Jacco, 'Server could not be
    resolved error' ???????)


    Harry Guest

  3. #3

    Default Re: Check in a Stored Procedure What violations have been done

    Hi Harry,

    If I understand you correctly, you use this stored procedure to import data
    from other system DBASE III apps and all. My approach would be to import the
    data in a staging table, create all the data in the foreign key tables that
    has to be created, and as the last step insert/update the data in the
    TS_NatFeat_Figure table.

    And I am not sure if you use the same stored procedure to process user input
    and the input that originates in other systems, but if you do I would split
    it in 2 stored procedures and remove the checking from the stored procedure
    that is used to process the user input.

    What do you actually do with the rows you import from DBASE etc that are not
    correct? Do you have to set up all the other information (countries,
    regions, distibutors etc) first? You might have a look at using DTS to
    import the data and have it write the exception to an exception file which
    you can handle after the import.


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


    "Harry Leboeuf" <moc.silopenikfueobelh> wrote in message
    news:phx.gbl... 
    every 
    the 
    there 
    be 


    Jacco Guest

  4. #4

    Default Re: Check in a Stored Procedure What violations have been done

    I know this would be a way, I was mostly looking for a way to 'trap' the
    kind of violation (if know that 547 is the right one) but even more what
    violation i'm doing within my procedure.
    I could test error = 547 but with that i'm still not knowing what was the
    name of the violation, and i can't find a variable containing the name of
    the violation.

    As i can't find it, i think the temporary table approach will be the better
    one.

    Thx


    Harry Guest

  5. #5

    Default Re: Check in a Stored Procedure What violations have been done

    Hi Harry,

    Only the error number is available in T-SQL. The complete error message is
    not available, even though it is avaialable in most client libraries like
    ADO, ODBC, OLE DB etc.


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


    "Harry Leboeuf" <moc.silopenikfueobelh> wrote in message
    news:phx.gbl... 
    the 
    of 
    better 


    Jacco Guest

Similar Threads

  1. new 2 stored procedure
    By emmim44 in forum Coldfusion Database Access
    Replies: 19
    Last Post: March 29th, 09:27 PM
  2. Stored procedure from stored procedure
    By Red Valsen in forum Informix
    Replies: 3
    Last Post: October 2nd, 02:22 PM
  3. Help on a VERY BAD Stored procedure
    By Jon in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 8th, 01:11 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