त्रुटियों की जानकारी देना

SQL Server में Transactions और Error Handling

Miriam Antona

Software Engineer

त्रुटि आना - पुनरावलोकन

INSERT INTO products (product_name, stock, price)
    VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);

Msg 2627, Level 14, State 1, Line 1
UNIQUE KEY constraint 'unique_name' का उल्लंघन.
'dbo.products' ऑब्जेक्ट में duplicate key insert नहीं कर सकते.
Duplicate key value है (Trek Powerfly 5 - 2018).

त्रुटि आना - पुनरावलोकन

BEGIN TRY
    INSERT INTO products (product_name, stock, price)
        VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
    SELECT 'Product inserted correctly!' AS message;
END TRY
BEGIN CATCH
    SELECT 'An error occurred! You are in the CATCH block' AS message;  
END CATCH

| message                                       |
|-----------------------------------------------|
| An error occurred! You are in the CATCH block |

Error फंक्शन

ERROR_NUMBER() त्रुटि का नंबर लौटाता है.

ERROR_SEVERITY() त्रुटि की severity (11-19) लौटाता है.

ERROR_STATE() त्रुटि का state लौटाता है.

ERROR_LINE() त्रुटि वाली line नंबर लौटाता है.

ERROR_PROCEDURE() stored procedure/trigger का नाम लौटाता है. अगर stored procedure/trigger नहीं है तो NULL.

ERROR_MESSAGE() त्रुटि संदेश का टेक्स्ट लौटाता है.

Error फंक्शन - उदाहरण

BEGIN TRY
    INSERT INTO products (product_name, stock, price)
        VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
END TRY
BEGIN CATCH
    SELECT  ERROR_NUMBER() AS Error_number,  
            ERROR_SEVERITY() AS Error_severity,  
            ERROR_STATE() AS Error_state,  
            ERROR_PROCEDURE() AS Error_procedure,  
            ERROR_LINE() AS Error_line,  
            ERROR_MESSAGE() AS Error_message;             
END CATCH

| Error_number| Error_severity| Error_state| Error_procedure| Error_line| Error_message                                       |
|-------------|---------------|------------|----------------|-----------|-----------------------------------------------------|
| 2627        | 14            | 1          | NULL           | 2         | Violation of UNIQUE KEY constraint 'unique_name'... |

Error फंक्शन - उदाहरण

पिछला आउटपुट

| Error_number| Error_severity| Error_state| Error_procedure| Error_line| Error_message                                       |
|-------------|---------------|------------|----------------|-----------|-----------------------------------------------------|
| 2627        | 14            | 1          | NULL           | 2         | Violation of UNIQUE KEY constraint 'unique_name'... |

मूल त्रुटि जानकारी

Msg 2627, Level 14, State 1, Line 1
UNIQUE KEY constraint 'unique_name' का उल्लंघन. 'dbo.products' ऑब्जेक्ट में duplicate key insert नहीं कर सकते. Duplicate key 
value है (Trek Powerfly 5 - 2018).

Error फंक्शन - उदाहरण

SELECT  ERROR_NUMBER() AS Error_number,  
        ERROR_SEVERITY() AS Error_severity,  
        ERROR_STATE() AS Error_state,  
        ERROR_PROCEDURE() AS Error_procedure,  
        ERROR_LINE() AS Error_line,  
        ERROR_MESSAGE() AS Error_message;

| Error_number | Error_severity | Error_state | Error_procedure | Error_line | Error_message |
|--------------|----------------|-------------|-----------------|------------|---------------|
| NULL         | NULL           | NULL        | NULL            | NULL       | NULL          |

Nested TRY...CATCH में Error फंक्शन

BEGIN TRY
    INSERT INTO products (product_name, stock, price)
        VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
END TRY
BEGIN CATCH
    BEGIN TRY
        INSERT INTO myErrors
            VALUES ('ERROR!')
    END TRY    
    BEGIN CATCH
        SELECT  'Outer CATCH block' AS 'Error_from',
            ERROR_NUMBER() AS Error_number,       
            ERROR_MESSAGE() AS Error_message; 
    END CATCH    
END CATCH

| Error_from        | Error_number | Error_message                             |
|-------------------|--------------|-------------------------------------------|
| Outer CATCH block | 8152         | String or binary data would be truncated. |

अभ्यास करते हैं!

SQL Server में Transactions और Error Handling