Ge information om fel
Transaktioner och felhantering i SQL Server
Miriam Antona
Software Engineer
Att få ett fel – repetition
INSERT INTO products (product_name, stock, price)
VALUES ('Trek Powerfly 5 - 2018', 10, 3499.99);
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'unique_name'.
Cannot insert duplicate key in object 'dbo.products'.
The duplicate key value is (Trek Powerfly 5 - 2018).
Att få ett fel – repetition
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 |
Felfunktioner
ERROR_NUMBER() returnerar felets nummer.
ERROR_SEVERITY() returnerar felets allvarlighetsgrad (11–19).
ERROR_STATE() returnerar felets tillstånd.
ERROR_LINE() returnerar radnumret där felet uppstod.
ERROR_PROCEDURE() returnerar namnet på den lagrade proceduren/utlösaren. NULL om ingen lagrad procedur/utlösare finns.
ERROR_MESSAGE() returnerar felmeddelandet som text.
Felfunktioner – exempel
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'... |
Felfunktioner – exempel
Senaste utdata
| Error_number| Error_severity| Error_state| Error_procedure| Error_line| Error_message |
|-------------|---------------|------------|----------------|-----------|-----------------------------------------------------|
| 2627 | 14 | 1 | NULL | 2 | Violation of UNIQUE KEY constraint 'unique_name'... |
Ursprunglig felinformation
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'unique_name'. Cannot insert duplicate key in object 'dbo.products'. The duplicate key
value is (Trek Powerfly 5 - 2018).
Felfunktioner – exempel
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 |
Felfunktioner i nästlade TRY...CATCH-konstruktioner
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. |
Nu kör vi en övning!
Transaktioner och felhantering i SQL Server
