Error Handling

  • Is it possible to get error record from a table? Not error_line() function. Say there are 1000 rows in a table and i join it with other 100 lines for an insert operation. If there are any error occured, can i get that error row from the table.

  • Not sure what kind of error you're talking about. Sounds like you want to know if records fail to join, but that wouldn't be an error ... it would just be two records that aren't joined. Maybe you can be a bit more specific.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Im currently migrating one if my existing application. In existing app. the error handling is done row by row. Data from text file have to be loaded in sql with lot of business rules. On doing this if any error found, that particular record will be written in error file.

    Is this possible in stored procedures?

  • As you havn't posted any sample data or table srtucture this example below might not be what your looking for but might give you some inspiration.

    It is a cursor which could be slow depending on the number of rows your talking about. You would import your text file to varchar or nvarchar columns using SSIS, bcp etc. But include two extra colimns IsValid and ErrorNum.

    Then test each constraint i.e can you convert a column to datetime, if so check the next row if not set the row as invalid and set the error num.

    Once complete query the table for bad data, fix the problems, retest then move it to wherever it needs to go.

    --Create a test table

    IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'ImportedData')

    DROP TABLE ImportedData

    CREATE Table ImportedData (

    Col1DateValueString varchar(255),

    IsValid bit NULL,

    ErrorNum int NULL)

    --Create test data

    INSERT INTO ImportedData (Col1DateValueString)

    SELECT '01-01-2010 15:38:09.650' UNION ALL

    SELECT '2010-07-01 15:38:09.650' UNION ALL

    SELECT '10 August 2010' UNION ALL

    SELECT 'Invalid Data' UNION ALL

    SELECT '2010-08-02 15:38:09.650' UNION ALL

    SELECT 'A12B&^*' UNION ALL

    SELECT '2010-07-01 15:38:09.650'

    -- Temporarily add a id column to ImportedData table to allow for updates

    ALTER TABLE ImportedData ADD ID INT IDENTITY(1,1)

    GO

    --Declare Variables for use throughout the data testing

    DECLARE @ID int,

    @STRING varchar(255),

    @test1 datetime

    -- Use a cursor test each conversion constraint

    DECLARE test_validity_cursor CURSOR

    FOR SELECT ID, Col1DateValueString

    FROM ImportedData

    OPEN test_validity_cursor

    FETCH test_validity_cursor INTO @ID, @STRING

    WHILE @@FETCH_STATUS = 0

    BEGIN

    -- Test String to see if its a valid date value

    BEGIN TRY

    SELECT @test1 = CAST(@STRING AS datetime)

    UPDATE ImportedData

    SET IsValid = 1

    END TRY

    --If test of date causes error update the IsValid and ErrorNum fields of the row.

    BEGIN CATCH

    UPDATE ImportedData

    SET IsValid = 0,

    ErrorNum = ERROR_NUMBER()

    WHERE ID = @ID

    END CATCH

    FETCH test_validity_cursor INTO @ID, @STRING

    END

    CLOSE test_validity_cursor

    DEALLOCATE test_validity_cursor

    --Remove the ID Column From the Staging table

    ALTER TABLE ImportedData DROP COLUMN ID

    GO

    --View Invalid Data

    SELECT * FROM ImportedData

    MCITP SQL 2005, MCSA SQL 2012

  • Karthikeyan-418364 (7/1/2010)


    Im currently migrating one if my existing application. In existing app. the error handling is done row by row. Data from text file have to be loaded in sql with lot of business rules. On doing this if any error found, that particular record will be written in error file.

    Is this possible in stored procedures?

    You can do a couple of things:

    1. Use SSIS and handle all the evaluations as part of the process of importing from the text file. This is good if the evaluation of each row is self-contained (not dependent on any other row.)

    2. Do a full import of the text file into an intermediary table and handle the translation of the final results to your regular table(s) via set-based processing. (Very very rarely will row-by-row be either necessary or desirable.)

    Without knowing anything about your structure or what kind of rules you're wanting to enforce I cannot be any more specific than that.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 5 posts - 1 through 4 (of 4 total)

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