Post reply

Trapping errors while truncating a table

  • May 9, 2006 at 12:37 pm

    #152960

    Hi,

    I need to write a stored procedure for a web application that simply truncates a table from the database after a certain activity. 

    I need help in trapping errors that go with Truncate Table statement.  @@ Error does not seem to work here. 

    What I am looking for is something like

    Truncate table TABLE

    IF there is an error

    PRint 'Error!'

     

    Thanks

    KR

  • May 9, 2006 at 12:42 pm

    #636845

    Not all errors are trappable in SQL server, especially TRUNCATE TABLE is something that should be thought very well  due to the elevated priviledges required. For such control you will need Client side code.

     


    * Noel

  • May 9, 2006 at 1:07 pm

    #636858

    Do a count(*), or some other potentially more efficient but predictable operation which returns a rowcount after the truncate.  If you have rows, the truncate failed and you can raise the error.

    And then again, I might be wrong ...
    David Webb

  • May 9, 2006 at 1:35 pm

    #636872

    Thanks to both of you for you input

    KR

  • May 9, 2006 at 2:35 pm

    #636887

    Hmmm how about something like:

    DECLARE @i INT

    SELECT TOP 1 @i = clustered_index_column FROM mytable

    IF @i IS NOT NULL

    BEGIN

        RAISERROR ...

    END

    Assumes your clustered_index_column is non-nullable of course.

  • May 10, 2006 at 12:35 am

    #636928

    IF EXISTS(SELECT 1 FROM mytable)

     RAISERROR (...)

    No need for variables or anything.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • May 10, 2006 at 7:38 am

    #636994

    hehe Excellente!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply