title
Error handling in sql server Part 56

description
In this video we will lean about handling errors in sql server 2005, 2008 and 20012. Error handling is also referred to as exception handling. Try catch blocks can be used to handle errors in sql server 2005 and later versions. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/10/error-handling-in-sql-server-2005-and_6.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/09/part-56-error-handling-in-sql-server.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Error handling in sql server Part 56', 'heatmap': [{'end': 665.816, 'start': 616.402, 'weight': 0.773}, {'end': 867.371, 'start': 853.899, 'weight': 0.769}, {'end': 1024.659, 'start': 988.516, 'weight': 0.807}], 'summary': 'Covers error handling in sql server, including the introduction of try-catch blocks in sql server 2005 and later versions, practical examples of error handling with tblproduct and tblproductsales tables, and the use of try-catch blocks for ensuring data consistency and facilitating error detection and handling in sql.', 'chapters': [{'end': 47.378, 'segs': [{'end': 47.378, 'src': 'embed', 'start': 0.069, 'weight': 0, 'content': [{'end': 1.95, 'text': 'Hello Welcome to Prejima Technologies.', 'start': 0.069, 'duration': 1.881}, {'end': 2.851, 'text': "I'm Venkat.", 'start': 2.27, 'duration': 0.581}, {'end': 5.192, 'text': 'This is part 55 of SQL Server.', 'start': 3.111, 'duration': 2.081}, {'end': 11.035, 'text': "In this session, we'll learn about handling errors in SQL Server 2005 and later versions.", 'start': 6.092, 'duration': 4.943}, {'end': 16.858, 'text': 'Before continuing with the session, I strongly recommend to watch part 18 and 55 of this video series.', 'start': 11.495, 'duration': 5.363}, {'end': 22.161, 'text': 'With the introduction of try-catch blocks in SQL Server 2005,,', 'start': 18.299, 'duration': 3.862}, {'end': 27.724, 'text': 'error handling in SQL Server is now very much similar to programming languages like C-sharp and Java.', 'start': 22.161, 'duration': 5.563}, {'end': 33.59, 'text': 'In SQL Server 2000, to handle errors, we use editErrorSystem function.', 'start': 28.587, 'duration': 5.003}, {'end': 42.015, 'text': 'In fact, we have seen in part 55 of this video series how to handle errors using editErrorSystem function.', 'start': 34.13, 'duration': 7.885}, {'end': 47.378, 'text': "If you haven't watched that part, I would strongly encourage you to do so before continuing with the session.", 'start': 42.535, 'duration': 4.843}], 'summary': 'Learn error handling in sql server 2005 and later versions, similar to c-sharp and java, with try-catch blocks.', 'duration': 47.309, 'max_score': 0.069, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg69.jpg'}], 'start': 0.069, 'title': 'Handling errors in sql server', 'summary': 'Delves into handling errors in sql server 2005 and later versions, highlighting the introduction of try-catch blocks, similarity to programming languages like c-sharp and java, and the use of editerrorsystem function in sql server 2000.', 'chapters': [{'end': 47.378, 'start': 0.069, 'title': 'Handling errors in sql server', 'summary': 'Discusses handling errors in sql server 2005 and later versions, emphasizing the introduction of try-catch blocks, similarity to programming languages like c-sharp and java, and the use of editerrorsystem function in sql server 2000.', 'duration': 47.309, 'highlights': ['The introduction of try-catch blocks in SQL Server 2005 has made error handling in SQL Server very much similar to programming languages like C-sharp and Java.', 'In SQL Server 2000, error handling was done using the editErrorSystem function.', 'The session recommends watching part 18 and 55 of the video series before continuing, emphasizing the importance of understanding error handling in SQL Server.']}], 'duration': 47.309, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg69.jpg', 'highlights': ['The introduction of try-catch blocks in SQL Server 2005 has made error handling in SQL Server very much similar to programming languages like C-sharp and Java.', 'In SQL Server 2000, error handling was done using the editErrorSystem function.', 'The session recommends watching part 18 and 55 of the video series before continuing, emphasizing the importance of understanding error handling in SQL Server.']}, {'end': 441.905, 'segs': [{'end': 82.591, 'src': 'embed', 'start': 48.159, 'weight': 2, 'content': [{'end': 53.862, 'text': 'In SQL Server 2005 and later, we can make use of this try-catch construct to handle errors.', 'start': 48.159, 'duration': 5.703}, {'end': 61.519, 'text': 'In fact, handling errors with try-catch construct is much easier than handling errors with editErrorSystem function.', 'start': 54.454, 'duration': 7.065}, {'end': 67.623, 'text': "Today, for this demo, we'll be making use of tblProduct and tblProductSales tables,", 'start': 62.179, 'duration': 5.444}, {'end': 72.326, 'text': 'the same tables that we have worked with in part 55 of this video series.', 'start': 67.623, 'duration': 4.703}, {'end': 82.591, 'text': 'tblProduct table is like an inventory table which contains the names of the products and their respective unit prices and the quantity available in stock.', 'start': 72.907, 'duration': 9.684}], 'summary': 'Using try-catch in sql server 2005 for error handling.', 'duration': 34.432, 'max_score': 48.159, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg48159.jpg'}, {'end': 236.069, 'src': 'embed', 'start': 211.984, 'weight': 0, 'content': [{'end': 221.342, 'text': 'Now, the next important thing is to check, OK, if the stock available is less than the quantity that we want to sell, then we are in a problem.', 'start': 211.984, 'duration': 9.358}, {'end': 224.864, 'text': 'We cannot continue with that transaction.', 'start': 222.682, 'duration': 2.182}, {'end': 230.667, 'text': 'So we have to throw an error back to the calling application stating that there is no enough stock available.', 'start': 224.904, 'duration': 5.763}, {'end': 232.027, 'text': "And that's what we are doing.", 'start': 230.987, 'duration': 1.04}, {'end': 236.069, 'text': 'And the way we are doing it is by using raiseError function.', 'start': 232.308, 'duration': 3.761}], 'summary': 'Ensuring sufficient stock availability for selling by using raiseerror function.', 'duration': 24.085, 'max_score': 211.984, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg211984.jpg'}, {'end': 353.071, 'src': 'embed', 'start': 324.629, 'weight': 1, 'content': [{'end': 326.451, 'text': 'We have updated this TBL product table.', 'start': 324.629, 'duration': 1.822}, {'end': 330.334, 'text': 'The next thing is to insert a row into this TBL product sales table.', 'start': 326.531, 'duration': 3.803}, {'end': 337.019, 'text': 'And obviously, to insert a row into this table, we need values for three columns, product ID, quantity sold, and product sales ID.', 'start': 330.354, 'duration': 6.665}, {'end': 341.574, 'text': 'Product ID and quantity sold are coming into the procedure as parameters.', 'start': 337.708, 'duration': 3.866}, {'end': 344.258, 'text': "So there's no problem in retrieving those values.", 'start': 341.634, 'duration': 2.624}, {'end': 347.723, 'text': 'But product sales ID is the primary key column.', 'start': 344.678, 'duration': 3.045}, {'end': 349.866, 'text': 'And it is not an identity column.', 'start': 348.043, 'duration': 1.823}, {'end': 353.071, 'text': 'So we will have to supply a value for that manually.', 'start': 350.227, 'duration': 2.844}], 'summary': 'Updating tbl product table and inserting a row into tbl product sales table, requiring manual value for product sales id.', 'duration': 28.442, 'max_score': 324.629, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg324629.jpg'}], 'start': 48.159, 'title': 'Error handling and product sales update in sql server', 'summary': 'Explores error handling through try-catch construct in sql server with practical examples using tblproduct and tblproductsales tables. it also explains the process of updating tbl product table and avoiding primary key violation errors in the tbl product sales table.', 'chapters': [{'end': 258.357, 'start': 48.159, 'title': 'Handling errors with try-catch in sql server', 'summary': 'Explores the use of try-catch construct for error handling in sql server, using tblproduct and tblproductsales tables to illustrate the process of selling products and the stored procedure sp sell product for checking available stock and throwing errors if stock is insufficient.', 'duration': 210.198, 'highlights': ['The stored procedure SP sell product is used to check the stock available for a product and throw an error if the stock is insufficient, using the raiseError function with a severity level of 16.', 'The try-catch construct in SQL Server is emphasized as being much easier for error handling compared to the editErrorSystem function.', 'The tblProduct table contains product names, unit prices, and available quantities, while the tblProductSales table records information about products sold, such as product ID and quantity sold.']}, {'end': 441.905, 'start': 259.197, 'title': 'Updating product sales procedure', 'summary': 'Explains the process of updating the tbl product table by subtracting the quantity of the product being sold and then inserting a row into the tbl product sales table, ensuring the manual calculation of the product sales id to avoid primary key violation errors.', 'duration': 182.708, 'highlights': ['The chapter explains the process of updating the TBL product table by subtracting the quantity of the product being sold.', 'The process of inserting a row into the TBL product sales table is outlined, including the manual calculation of the product sales ID to avoid primary key violation errors.']}], 'duration': 393.746, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg48159.jpg', 'highlights': ['The stored procedure SP sell product checks stock and throws error if insufficient.', 'The try-catch construct in SQL Server is emphasized for error handling.', 'The tblProduct table contains product names, unit prices, and available quantities.', 'The process of updating the TBL product table by subtracting the quantity of the product being sold.', 'The process of inserting a row into the TBL product sales table is outlined.']}, {'end': 1064.7, 'segs': [{'end': 534.325, 'src': 'embed', 'start': 486.117, 'weight': 0, 'content': [{'end': 490.101, 'text': "And let's say we want to sell, you know, 10 desktops.", 'start': 486.117, 'duration': 3.984}, {'end': 496.486, 'text': 'And to do that, to this stored procedure, SP sell product, we need to pass in values for the two parameters, product ID.', 'start': 490.481, 'duration': 6.005}, {'end': 499.005, 'text': 'The product ID is 2.', 'start': 497.027, 'duration': 1.978}, {'end': 501.646, 'text': 'And the quantity that I want to sell is 10.', 'start': 499.005, 'duration': 2.641}, {'end': 504.868, 'text': "So let's execute this procedure and see what's going to happen.", 'start': 501.646, 'duration': 3.222}, {'end': 506.188, 'text': 'So one row affected.', 'start': 505.168, 'duration': 1.02}, {'end': 507.589, 'text': 'Now let us see.', 'start': 506.208, 'duration': 1.381}, {'end': 512.712, 'text': "So as expected, desktop's quantity available is 40.", 'start': 509.03, 'duration': 3.682}, {'end': 515.773, 'text': "And there's a row inserted into TBL product sales.", 'start': 512.712, 'duration': 3.061}, {'end': 520.395, 'text': "Now let's try to manually insert an error into this procedure.", 'start': 516.193, 'duration': 4.202}, {'end': 524.197, 'text': 'And the way we can do it is simply comment this line.', 'start': 520.794, 'duration': 3.403}, {'end': 530.764, 'text': 'So the place where we are actually incrementing the primary key value by 1, we are commenting that line.', 'start': 524.881, 'duration': 5.883}, {'end': 534.325, 'text': "So obviously, what's going to happen now, we will get a primary key violation.", 'start': 531.144, 'duration': 3.181}], 'summary': 'Selling 10 desktops using stored procedure, 1 row affected, 40 quantity available, primary key violation when error inserted.', 'duration': 48.208, 'max_score': 486.117, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg486117.jpg'}, {'end': 665.816, 'src': 'heatmap', 'start': 616.402, 'weight': 0.773, 'content': [{'end': 618.923, 'text': "So if you look at this, it's exactly the same procedure.", 'start': 616.402, 'duration': 2.521}, {'end': 625.727, 'text': 'All we are doing here is the update TBL product statement.', 'start': 619.464, 'duration': 6.263}, {'end': 631.951, 'text': 'and calculating of the primary key value and inserting into TBL product sales.', 'start': 626.81, 'duration': 5.141}, {'end': 638.652, 'text': 'All this code is now wrapped inside a begin try and end try block.', 'start': 632.691, 'duration': 5.961}, {'end': 642.773, 'text': 'So when these statements are being executed?', 'start': 639.873, 'duration': 2.9}, {'end': 643.233, 'text': 'if at all?', 'start': 642.773, 'duration': 0.46}, {'end': 647.074, 'text': "if there is any error situation, what's going to happen?", 'start': 643.233, 'duration': 3.841}, {'end': 650.915, 'text': 'the control will immediately jump to the catch block.', 'start': 647.074, 'duration': 3.841}, {'end': 658.13, 'text': 'And then if you look at in the catch block, what we are saying in the first statement is to roll back the transaction.', 'start': 651.986, 'duration': 6.144}, {'end': 659.972, 'text': 'So we are rolling the transaction back.', 'start': 658.17, 'duration': 1.802}, {'end': 665.816, 'text': 'So which means, even if this statement has executed successfully, that will also be rolled back.', 'start': 660.392, 'duration': 5.424}], 'summary': 'Updating tbl product statement with error handling and rollback.', 'duration': 49.414, 'max_score': 616.402, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg616402.jpg'}, {'end': 884.008, 'src': 'heatmap', 'start': 853.899, 'weight': 0.769, 'content': [{'end': 854.34, 'text': 'All right.', 'start': 853.899, 'duration': 0.441}, {'end': 858.323, 'text': "So now let's quickly execute the stored procedure and see what's going to happen.", 'start': 854.7, 'duration': 3.623}, {'end': 862.467, 'text': "So let's see what's there at the moment.", 'start': 860.325, 'duration': 2.142}, {'end': 864.809, 'text': 'So at the moment, we have 70 laptops.', 'start': 862.767, 'duration': 2.042}, {'end': 867.371, 'text': "So let's go ahead and sell 10 laptops.", 'start': 864.849, 'duration': 2.522}, {'end': 870.213, 'text': "So I'm selling 10 laptops.", 'start': 868.712, 'duration': 1.501}, {'end': 880.387, 'text': "So at the moment everything should work fine, because if you look at the stored procedure, you know we haven't commented this line,", 'start': 870.974, 'duration': 9.413}, {'end': 882.848, 'text': 'which would generate that primary key violation error.', 'start': 880.387, 'duration': 2.461}, {'end': 884.008, 'text': 'So it should work.', 'start': 883.168, 'duration': 0.84}], 'summary': 'Executing stored procedure: 70 laptops; selling 10. no errors expected.', 'duration': 30.109, 'max_score': 853.899, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg853899.jpg'}, {'end': 1024.659, 'src': 'heatmap', 'start': 988.516, 'weight': 0.807, 'content': [{'end': 995.059, 'text': 'So to find out which line is that, press Ctrl G and then type in the line number and click OK.', 'start': 988.516, 'duration': 6.543}, {'end': 999.081, 'text': 'It will take you to the line number and your cursor will be blinking there.', 'start': 995.419, 'duration': 3.662}, {'end': 1004.826, 'text': 'Okay, so if this is not clear and this is the line which caused that?', 'start': 1000.703, 'duration': 4.123}, {'end': 1011.71, 'text': 'if this is not clear, another way to show the line numbers is go to Tools Options and in Text Editor,', 'start': 1004.826, 'duration': 6.884}, {'end': 1015.413, 'text': 'select Transacts Equal General and select Line Numbers.', 'start': 1011.71, 'duration': 3.703}, {'end': 1016.113, 'text': 'click OK.', 'start': 1015.413, 'duration': 0.7}, {'end': 1017.894, 'text': 'It should show the line number.', 'start': 1016.493, 'duration': 1.401}, {'end': 1024.659, 'text': 'So if you look at the line number, it is saying error line is 34.', 'start': 1020.836, 'duration': 3.823}], 'summary': 'To find error line, press ctrl g, type line number, and click ok. another way: go to tools options, select text editor, transacts equal general, and line numbers, then click ok. the error line is 34.', 'duration': 36.143, 'max_score': 988.516, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg988516.jpg'}], 'start': 446.075, 'title': 'Using try-catch blocks in sql for error handling', 'summary': 'Discusses the use of try-catch blocks in sql for error handling, demonstrating scenarios of transaction failures and primary key violation errors, and how try-catch blocks ensure data consistency and facilitate error detection and handling.', 'chapters': [{'end': 776.376, 'start': 446.075, 'title': 'Error handling with try-catch blocks', 'summary': 'Discusses the use of try-catch blocks in sql for error handling, demonstrating a scenario where a transaction involving updates and inserts fails due to errors, and how try-catch blocks can be used to detect and handle these errors, ensuring data consistency and transaction rollback.', 'duration': 330.301, 'highlights': ['The chapter demonstrates a scenario where a transaction involving updates and inserts fails due to errors, showing how try-catch blocks can be used to detect and handle these errors, ensuring data consistency and transaction rollback.', 'The demonstration involves attempting to sell 10 desktops, resulting in a successful update of the quantity available, followed by an intentional error insertion leading to a primary key violation in a subsequent attempt to sell 10 more desktops.', 'The use of try-catch blocks is highlighted as a much easier approach compared to using editError, allowing for the immediate detection and handling of errors within the context of the catch block, ensuring transaction rollback and data consistency.', 'The discussion emphasizes the functionality of built-in SQL Server functions within the catch block, such as Error Number and Error Message, which provide meaningful information about errors, but return null when executed outside the context of the catch block.']}, {'end': 1064.7, 'start': 776.396, 'title': 'Understanding try-catch blocks in sql', 'summary': 'Explains the usage of try-catch blocks in sql, highlighting the behavior of exceptions, error handling, and the retrieval of error information, with an example of primary key violation error and its handling.', 'duration': 288.304, 'highlights': ['The try-catch block in SQL is used to handle exceptions, where any set of SQL statements wrapped between begin, try and end try will immediately go to the catch block if an exception occurs, otherwise, it will proceed to the statement after the catch block.', 'Errors trapped by catch blocks are not returned to the calling application, and if any error information must be returned, the code in the catch block should use the raiseError function to do so.', 'The chapter demonstrates the execution of a stored procedure, selling of laptops, encountering a primary key violation error, trapping it inside a catch block, and retrieving detailed error information such as error message, procedure name, error state, error severity, and error line number.']}], 'duration': 618.625, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/VLDirfx_OQg/pics/VLDirfx_OQg446075.jpg', 'highlights': ['The use of try-catch blocks ensures data consistency and facilitates error detection and handling', 'The try-catch block in SQL immediately goes to the catch block if an exception occurs', 'Errors trapped by catch blocks are not returned to the calling application', 'Built-in SQL Server functions within the catch block provide meaningful information about errors']}], 'highlights': ['The introduction of try-catch blocks in SQL Server 2005 has made error handling in SQL Server very much similar to programming languages like C-sharp and Java.', 'The use of try-catch blocks ensures data consistency and facilitates error detection and handling', 'The stored procedure SP sell product checks stock and throws error if insufficient.', 'The session recommends watching part 18 and 55 of the video series before continuing, emphasizing the importance of understanding error handling in SQL Server.', 'The try-catch construct in SQL Server is emphasized for error handling.', 'In SQL Server 2000, error handling was done using the editErrorSystem function.', 'The tblProduct table contains product names, unit prices, and available quantities.', 'The process of updating the TBL product table by subtracting the quantity of the product being sold.', 'The process of inserting a row into the TBL product sales table is outlined.', 'The try-catch block in SQL immediately goes to the catch block if an exception occurs', 'Errors trapped by catch blocks are not returned to the calling application', 'Built-in SQL Server functions within the catch block provide meaningful information about errors']}