Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Try Catch

  1. #1
    Join Date
    Dec 2005
    Posts
    266

    Unanswered: Try Catch

    Hi

    for MS SQL 2000
    how can I do ?:


    INSERT INTO [Users] (Name)
    SELECT Names FROM OtherUsers


    I am having an UNIQUE INDEX on [Users].Name

    how can I avoid an error ?
    if the [Users].Name allready exists I want to jump over the error and keep on inserting

    thank you for helping

  2. #2
    Join Date
    Feb 2003
    Location
    India
    Posts
    216
    INSERT INTO [Users] (Name)
    SELECT distinct Names FROM OtherUsers

  3. #3
    Join Date
    Dec 2005
    Posts
    266
    no it cannot work in that way , if a Name exists allready In Users a distinct on OtherUsers will change nothing
    i need a try catch on Users

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Insert Into [users](name)
    Select Names From Otherusers
    Where Names Not In (select Users.name
    From Users Inner Join Otherusers On Users.name=otherusers.names)
    Inspiration Through Fermentation

  5. #5
    Join Date
    Dec 2005
    Posts
    266
    thank you RedNeckGeek it works fine

    but is there a general way to do that with something like a try catch

    when using .NET

    Try
    RunCommand("INSERT Any")
    Catch
    End TRy

    solve any INSERT database problem

    I am looking for the same directly in SQL langage

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    T-SQL Try/catch was only introduced in SQL 2005.

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    in 2000, error handling pretty much sucks. 2005 improved greatly with try/catch.

    these articles are worth reading:

    http://sommarskog.se/error-handling-I.html
    http://sommarskog.se/error-handling-II.html

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I'm going to switch from "answerer" to "asker"...

    What is "Try/Catch" supposed to do?
    Inspiration Through Fermentation

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    try/catch is a programming construct you use in languages like C++ to handle errors. if an exception is thrown in a try block, control is immediately passed to the catch block where you can handle the error.

    2005 introduced the same concept in t-sql. http://msdn2.microsoft.com/en-us/library/ms175976.aspx

  10. #10
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    The TRY...CATCH will not solve your problem. The moment you try to insert a name that already exists you will end up in the CATCH, the statement will already have been rolled back. So no insert will be done, not even for the ones you want.

    @RenNeckGeek: why the INNER JOIN, this should work as wel:
    Code:
    INSERT INTO [users](name)
    SELECT Names 
    FROM Otherusers
    WHERE Names NOT IN (SELECT name FROM Users)
    Or
    Code:
    INSERT INTO [users](name)
    SELECT o.Names 
    FROM Otherusers o
       INNER JOIN users u ON u.name = o.Names
    WHERE u.name IS NULL
    BTW. The TRY...CATCH is to catch errors, is good programming to always avoid errors. So you should not build your code depending on errors to work correctly!

  11. #11
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    agreed. try/catch should never be used for flow control. exceptions are supposed to be exceptional.

    also there's a perf cost to catching exceptions (well, there is in C++ and C# anyway, not sure about sql) so don't use it for flow control! did i say that already?

  12. #12
    Join Date
    Dec 2005
    Posts
    266
    thank you

    Lexiflex is good programming to always avoid errors. in the real world it is not allways possible
    jezemine in a very big application exceptions are a part of the programm

    try catch is a perfect way to avoid head-hake when you dont know what will come frome another source, it is not necessary a way to do something when there is a problem

    try
    do instruction 1
    catch
    try
    do instruction 2
    catch
    forget it
    end try
    end try

    if you have no instruction in the catch part the programm will keep on runing
    if you want to stop instructions you must use finally in the try-catch block

    i thought in SQL 2000 it was possible to use if @@error <> 0 keep on and forget it

    thank you
    Last edited by anselme; 01-16-07 at 02:32.

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    "in a very big application exceptions are a part of the programm"

    Huh? all I said is you shouldn't use try/catch for flow control. do you know what that sentence means?

    basically, it means this: dont use try/catch if you can use if/else instead.

    and in your particular case, you can avoid try/catch becuse you can put an if exists() check before the insert.

  14. #14
    Join Date
    Dec 2005
    Posts
    266
    but that is just what i am asking !
    how will you use a if exists in that case ?

    if not exists (INSERT INTO [users](name)
    SELECT Names
    FROM OtherUsers
    )

    ??

    thank you

  15. #15
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    Correct me if I'm wrong but you want to insert all the names into a table under the condition they are not already present in the destination table?

    You don't need the IF EXISTS, you can use one of the statements I suggested.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •