title
Error handling in sql server 2000 Part 55

description
In this video we will learn about error handling in sql server 2000. Error handling is also referred to as exception handling. @@Error system function is used to handle errors in sql server 2000. Sometimes, system functions that begin with two at signs (@@), are called as global variables. They are not variables and do not have the same behaviours as variables, instead they are very similar to functions. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/10/error-handling-in-sql-server-2000-part.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-55-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 2000 Part 55', 'heatmap': [{'end': 433.243, 'start': 415.558, 'weight': 1}, {'end': 857.351, 'start': 835.53, 'weight': 0.804}, {'end': 1275.008, 'start': 1251.128, 'weight': 0.807}], 'summary': 'Series covers transitioning error handling in sql server from 2000 to 2005, managing product sales and stock including stock validation, error severity and inventory management involving error state parameters, managing sql transactions, and handling sql errors with examples and emphasis on immediate error checking and the usage of try-catch blocks in sql server 2005 and 2008.', 'chapters': [{'end': 61.616, 'segs': [{'end': 61.616, 'src': 'embed', 'start': 16.271, 'weight': 0, 'content': [{'end': 23.634, 'text': 'Before continuing with the session, I strongly recommend to watch part 18 of this video series where we have covered the basics of stored procedures.', 'start': 16.271, 'duration': 7.363}, {'end': 34.355, 'text': 'With the introduction of try-catch blocks in SQL Server 2005, error handling in SQL Server is similar to programming languages like C-sharp and Java.', 'start': 25.27, 'duration': 9.085}, {'end': 41.879, 'text': 'In SQL Server 2000,, to handle errors, we use a system function at at error,', 'start': 35.095, 'duration': 6.784}, {'end': 49.242, 'text': 'whereas in SQL Server 2005 and later versions we can make use of try-catch blocks, which we will talk about in the next session.', 'start': 41.879, 'duration': 7.363}, {'end': 56.713, 'text': 'Sometimes, these system functions that begin with at at sign are called as global variables.', 'start': 50.183, 'duration': 6.53}, {'end': 61.616, 'text': 'Remember, they are not variables and do not have the same behavior as variables.', 'start': 57.173, 'duration': 4.443}], 'summary': 'Sql server 2005 introduced try-catch blocks for error handling, unlike sql server 2000 which used system functions, as explained in part 18 of the video series.', 'duration': 45.345, 'max_score': 16.271, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta016271.jpg'}], 'start': 0.149, 'title': 'Transitioning error handling in sql server', 'summary': 'Covers the transition in error handling from sql server 2000 to sql server 2005 and later versions, introducing try-catch blocks and the use of system functions in error handling. it emphasizes the importance of watching part 18 of the video series on stored procedures before proceeding.', 'chapters': [{'end': 61.616, 'start': 0.149, 'title': 'Handling errors in sql server', 'summary': 'Covers the transition in error handling from sql server 2000 to sql server 2005 and later versions, introducing try-catch blocks and the use of system functions in error handling, stressing the importance of watching part 18 of the video series on stored procedures before proceeding.', 'duration': 61.467, 'highlights': ['With the introduction of try-catch blocks in SQL Server 2005, error handling in SQL Server is similar to programming languages like C-sharp and Java.', 'In SQL Server 2000, to handle errors, we use a system function at at error, whereas in SQL Server 2005 and later versions we can make use of try-catch blocks.', 'Sometimes, these system functions that begin with at at sign are called as global variables, they are not variables and do not have the same behavior as variables.', 'It is strongly recommended to watch part 18 of this video series where we have covered the basics of stored procedures before continuing with the session.']}], 'duration': 61.467, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0149.jpg', 'highlights': ['With the introduction of try-catch blocks in SQL Server 2005, error handling is similar to C-sharp and Java.', 'In SQL Server 2000, error handling uses system functions, while in SQL Server 2005 and later, try-catch blocks are used.', 'System functions starting with at sign are not variables and do not behave like variables.', 'It is strongly recommended to watch part 18 of the video series on stored procedures before proceeding.']}, {'end': 307.351, 'segs': [{'end': 127.322, 'src': 'embed', 'start': 83.271, 'weight': 0, 'content': [{'end': 91.076, 'text': 'product ID is the primary key, and this TBL product sales table is the table which contains the transactions that we made.', 'start': 83.271, 'duration': 7.805}, {'end': 100.263, 'text': 'for example, if I sell a product, then it holds information about what product did we sell, which is nothing but product ID, and what is the quantity,', 'start': 91.076, 'duration': 9.187}, {'end': 105.807, 'text': 'which is quantity sold, and product sales ID is the primary key for this table.', 'start': 100.263, 'duration': 5.544}, {'end': 110.27, 'text': 'so obviously, when we sell a product, there are two things that needs to happen.', 'start': 105.807, 'duration': 4.463}, {'end': 113.697, 'text': 'first, it needs to check The product that we are trying to sell.', 'start': 110.27, 'duration': 3.427}, {'end': 116.238, 'text': 'do we have enough quantity available?', 'start': 113.697, 'duration': 2.541}, {'end': 122.64, 'text': 'Once we determine that, we need to deduct the quantity that we are trying to sell from the quantity available.', 'start': 116.758, 'duration': 5.882}, {'end': 127.322, 'text': 'And then the next step is to insert a row into this TBL product sales table.', 'start': 123.281, 'duration': 4.041}], 'summary': 'Tbl product sales table tracks product transactions and updates quantities upon sales.', 'duration': 44.051, 'max_score': 83.271, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta083271.jpg'}, {'end': 221.136, 'src': 'embed', 'start': 174.932, 'weight': 1, 'content': [{'end': 177.553, 'text': 'with some processing steps in between.', 'start': 174.932, 'duration': 2.621}, {'end': 178.734, 'text': "Let's look at this.", 'start': 177.954, 'duration': 0.78}, {'end': 184.756, 'text': 'Now, this is the stored procedure which is basically selling products.', 'start': 180.235, 'duration': 4.521}, {'end': 191.719, 'text': 'Obviously, if a stored procedure has to sell products, it has to have two parameters.', 'start': 185.036, 'duration': 6.683}, {'end': 200.386, 'text': 'The product that we want to sell, the product ID that we want to sell, and the quantity that we want to sell, both of them of type integer.', 'start': 192.159, 'duration': 8.227}, {'end': 207.829, 'text': 'And if you look at this, the first thing this procedure is doing, check the stock available for the product that we want to sell.', 'start': 201.706, 'duration': 6.123}, {'end': 213.212, 'text': 'Now let us say, for example, I want to sell laptops 10.', 'start': 207.969, 'duration': 5.243}, {'end': 217.114, 'text': 'If you look at the product ID is 1, 1 is nothing but laptops.', 'start': 213.212, 'duration': 3.902}, {'end': 221.136, 'text': "And what's the quantity that we want to sell? We want to sell 10 of them.", 'start': 217.194, 'duration': 3.942}], 'summary': 'Stored procedure sells products with two integer parameters.', 'duration': 46.204, 'max_score': 174.932, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0174932.jpg'}], 'start': 62.237, 'title': 'Managing product sales and stock', 'summary': "Covers working with tbl product and tbl product sales tables, including the process of updating quantity available when selling a product. it also discusses a stored procedure for selling products, requiring two parameters: product id and quantity. additionally, it explains the process of product stock validation, handling scenarios where the desired quantity exceeds the available stock, with an instance of 90 laptops available and an attempt to sell 100 laptops resulting in an error message 'not enough stock available'.", 'chapters': [{'end': 148.877, 'start': 62.237, 'title': 'Working with tbl product and tbl product sales tables', 'summary': 'Covers the usage of tbl product and tbl product sales tables, explaining their roles and the process of updating quantity available when selling a product.', 'duration': 86.64, 'highlights': ['The TBL product table holds information about the products sold, their unit price, and the available quantity, with product ID as the primary key.', 'The TBL product sales table contains transaction details, such as the product sold and the quantity, with product sales ID as the primary key.', 'The process of selling a product involves checking the quantity available, deducting the sold quantity, and inserting a new row in the TBL product sales table.']}, {'end': 200.386, 'start': 148.877, 'title': 'Stored procedure for selling products', 'summary': 'Discusses a stored procedure for selling products, which updates the tbl product table and inserts a new row, requiring two parameters: product id and quantity, both of type integer.', 'duration': 51.509, 'highlights': ['The stored procedure updates the TBL product table and inserts a new row, facilitating the sales process with some processing steps in between.', 'The procedure requires two parameters: the product ID and the quantity, both of type integer.']}, {'end': 307.351, 'start': 201.706, 'title': 'Product stock validation', 'summary': "Explains the process of checking stock availability for a product, using laptops as an example, and handling the scenario when the desired quantity exceeds the available stock, with an instance of 90 laptops available and an attempt to sell 100 laptops resulting in an error message 'not enough stock available' and the usage of the raiseerror function.", 'duration': 105.645, 'highlights': ["The process involves checking the stock availability for a specific product, with an example of attempting to sell 100 laptops when only 90 are available, leading to an error message 'not enough stock available' and the usage of the raiseError function.", 'The variable stockAvailable is used to store the quantity of the product available in the stock, with an example of 90 laptops being available, and the attempt to sell 10 laptops.', "The chapter discusses the use of the raiseError function to handle custom errors, with an example of using it to throw an error message 'not enough stock available' when the desired quantity exceeds the available stock."]}], 'duration': 245.114, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta062237.jpg', 'highlights': ['The TBL product table holds product information with product ID as primary key.', 'The stored procedure requires two parameters: product ID and quantity.', 'The process involves checking stock availability for a specific product.', 'The TBL product sales table contains transaction details with product sales ID as primary key.', 'The process of selling a product involves checking available quantity and deducting sold quantity.']}, {'end': 778.201, 'segs': [{'end': 334.007, 'src': 'embed', 'start': 308.118, 'weight': 0, 'content': [{'end': 314.94, 'text': 'And then there are another two parameters error severity level and error state.', 'start': 308.118, 'duration': 6.822}, {'end': 318.822, 'text': 'Now, the severity level in most cases is 16.', 'start': 315.521, 'duration': 3.301}, {'end': 322.723, 'text': 'And 16 indicates general errors that can be corrected by the user.', 'start': 318.822, 'duration': 3.901}, {'end': 326.164, 'text': 'So most of the time.', 'start': 324.224, 'duration': 1.94}, {'end': 334.007, 'text': 'if you want to return custom errors, the severity level is 16,, which indicates that these are the errors which can be corrected by user.', 'start': 326.164, 'duration': 7.843}], 'summary': 'Error severity level mostly 16, indicating general errors correctable by user.', 'duration': 25.889, 'max_score': 308.118, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0308118.jpg'}, {'end': 384.535, 'src': 'embed', 'start': 356.303, 'weight': 1, 'content': [{'end': 366.65, 'text': 'But raise error, when we use raise error to throw a custom exception, custom error, the error state can only be a value between 1 and 127.', 'start': 356.303, 'duration': 10.347}, {'end': 369.811, 'text': 'but in general we use 1.', 'start': 366.65, 'duration': 3.161}, {'end': 373.572, 'text': 'so the error state is 1 here.', 'start': 369.811, 'duration': 3.761}, {'end': 379.834, 'text': 'so this will throw an exception if the stock available is less than the quantity that we want to sell.', 'start': 373.572, 'duration': 6.262}, {'end': 384.535, 'text': "on the other hand, if it's not, then it comes to the else part.", 'start': 379.834, 'duration': 4.701}], 'summary': 'Using raise error for custom exception with error state 1-127.', 'duration': 28.232, 'max_score': 356.303, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0356303.jpg'}, {'end': 484.804, 'src': 'heatmap', 'start': 415.558, 'weight': 2, 'content': [{'end': 421.096, 'text': 'So quantity available is equal to quantity available minus the quantity that we want to sell.', 'start': 415.558, 'duration': 5.538}, {'end': 424.138, 'text': 'In this case we want to sell 10 laptops.', 'start': 421.477, 'duration': 2.661}, {'end': 433.243, 'text': 'So what happens, from 90 we are subtracting 10 and updating this quantity available column for laptops row.', 'start': 424.298, 'duration': 8.945}, {'end': 436.205, 'text': "So that's what this update does.", 'start': 434.564, 'duration': 1.641}, {'end': 444.371, 'text': 'And then the next thing is After we update this TBL product table, we need to insert a new row into this table.', 'start': 436.725, 'duration': 7.646}, {'end': 449.273, 'text': 'Now, to insert a new row into TBL product sales table, you need values for three columns.', 'start': 444.491, 'duration': 4.782}, {'end': 454.595, 'text': "Product sales ID, obviously, that's the primary key, so you'll have to supply that.", 'start': 450.874, 'duration': 3.721}, {'end': 457.236, 'text': 'The product ID and quantity sold.', 'start': 454.975, 'duration': 2.261}, {'end': 462.438, 'text': 'The product ID and quantity sold are coming in as parameters to this procedure.', 'start': 457.536, 'duration': 4.902}, {'end': 468.999, 'text': 'So we have those product ID and quantity sold in a 1 and 10.', 'start': 463.519, 'duration': 5.48}, {'end': 471.6, 'text': 'But the third thing is the primary key itself.', 'start': 468.999, 'duration': 2.601}, {'end': 477.041, 'text': 'We need to calculate the primary key for this table that we need to insert.', 'start': 471.64, 'duration': 5.401}, {'end': 480.542, 'text': 'Now already, if you look at the key values, we have 1 and 2.', 'start': 477.081, 'duration': 3.461}, {'end': 484.804, 'text': 'So we need to calculate the next primary key, which is 3.', 'start': 480.542, 'duration': 4.262}], 'summary': 'Updating tbl product table and inserting new row with 10 laptops sold.', 'duration': 74.69, 'max_score': 415.558, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0415558.jpg'}, {'end': 731.705, 'src': 'embed', 'start': 701.013, 'weight': 5, 'content': [{'end': 709.079, 'text': 'So at the moment, if you look at the rows that we have in TBL product and TBL product sales tables, the product sales ID is 1.', 'start': 701.013, 'duration': 8.066}, {'end': 714.139, 'text': 'and what happens when we try to insert another row with the same ID?', 'start': 709.079, 'duration': 5.06}, {'end': 719.621, 'text': "we get a primary key violation error, and let's see what's gonna happen now.", 'start': 714.139, 'duration': 5.482}, {'end': 726.383, 'text': "so let's alter this stored procedure.", 'start': 719.621, 'duration': 6.762}, {'end': 731.705, 'text': "okay, so now let's try to sell, you know, 10 laptops again.", 'start': 726.383, 'duration': 5.322}], 'summary': 'Primary key violation error encountered when inserting row with same id. altering stored procedure to resolve issue.', 'duration': 30.692, 'max_score': 701.013, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0701013.jpg'}], 'start': 308.118, 'title': 'Error severity and inventory management', 'summary': 'Explains error severity levels, with most being level 16, and error state parameters, which range from 1 to 255 and are limited to 1 to 127 when using raise error for custom exceptions. it also details an inventory management procedure involving product table updates, primary key calculation, and row insertion, with an intentional primary key violation error.', 'chapters': [{'end': 384.535, 'start': 308.118, 'title': 'Error severity and state parameters', 'summary': 'Explains the error severity level and error state parameters, with the severity level mostly being 16, indicating correctable errors, and the error state being a number between 1 and 255, but when using raise error for custom exceptions, it can only be between 1 and 127, typically being 1.', 'duration': 76.417, 'highlights': ['The severity level in most cases is 16, indicating general errors that can be corrected by the user, and is typically used for returning custom errors.', 'The error state is a number between 1 and 255, but when using raise error for custom exceptions, it can only be a value between 1 and 127, typically being 1.']}, {'end': 778.201, 'start': 384.535, 'title': 'Inventory management procedure', 'summary': 'Explains an inventory management procedure that involves updating the product table with the quantity sold, calculating the next primary key for product sales table, and inserting a new row into the table, with an intentional error causing a primary key violation.', 'duration': 393.666, 'highlights': ['The procedure involves updating the product table with the quantity sold, for example, reducing the inventory from 90 to 80 when selling 10 laptops.', 'Calculating the next primary key for the product sales table, ensuring the new primary key value is generated based on existing data, such as incrementing from the max product sales ID.', 'Inserting a new row into the product sales table, for instance, adding a row with product ID 1 and quantity sold 10, and updating the available stock from 90 to 80.', 'Intentionally introducing an error in the procedure results in a primary key violation when trying to insert a row with an existing primary key value, demonstrating the impact of the error on the system.']}], 'duration': 470.083, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0308118.jpg', 'highlights': ['The severity level in most cases is 16, indicating general errors that can be corrected by the user, and is typically used for returning custom errors.', 'The error state is a number between 1 and 255, but when using raise error for custom exceptions, it can only be a value between 1 and 127, typically being 1.', 'The procedure involves updating the product table with the quantity sold, for example, reducing the inventory from 90 to 80 when selling 10 laptops.', 'Calculating the next primary key for the product sales table, ensuring the new primary key value is generated based on existing data, such as incrementing from the max product sales ID.', 'Inserting a new row into the product sales table, for instance, adding a row with product ID 1 and quantity sold 10, and updating the available stock from 90 to 80.', 'Intentionally introducing an error in the procedure results in a primary key violation when trying to insert a row with an existing primary key value, demonstrating the impact of the error on the system.']}, {'end': 1160.911, 'segs': [{'end': 868.596, 'src': 'heatmap', 'start': 835.53, 'weight': 0, 'content': [{'end': 839.214, 'text': 'Either both of them should succeed or none of them should work.', 'start': 835.53, 'duration': 3.684}, {'end': 843.165, 'text': 'If one of them fails, the other statement should also be rolled back.', 'start': 839.924, 'duration': 3.241}, {'end': 849.648, 'text': "And why were we not able to properly do this transaction? Because we didn't check for errors.", 'start': 844.426, 'duration': 5.222}, {'end': 857.351, 'text': 'So in SQL Server, how can we check for errors? As I told you, we use this system function at atError.', 'start': 850.048, 'duration': 7.303}, {'end': 865.215, 'text': 'let us see how to use that at at error system function, and then you know, roll back the transaction if there are errors.', 'start': 858.371, 'duration': 6.844}, {'end': 868.596, 'text': 'and if there are no errors, then we will commit the transaction.', 'start': 865.215, 'duration': 3.381}], 'summary': 'Ensure both statements succeed or rollback, check for errors using at aterror function in sql server.', 'duration': 41.275, 'max_score': 835.53, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0835530.jpg'}, {'end': 977.926, 'src': 'embed', 'start': 951.231, 'weight': 1, 'content': [{'end': 955.092, 'text': 'And remember, if there is an error, we want to roll back this entire transaction.', 'start': 951.231, 'duration': 3.861}, {'end': 957.093, 'text': "Again, we haven't spoken about transactions yet.", 'start': 955.152, 'duration': 1.941}, {'end': 962.054, 'text': "We'll be talking about transactions in a great detail in the later sessions of this video series.", 'start': 957.353, 'duration': 4.701}, {'end': 970.021, 'text': 'So if there are errors, we want to say rollback transaction.', 'start': 963.075, 'duration': 6.946}, {'end': 977.926, 'text': 'You can either use the complete word or you can just say rollback tran, just like how we have done here, begin tran, commit tran.', 'start': 970.642, 'duration': 7.284}], 'summary': 'Emphasize rollback on errors to ensure transaction integrity.', 'duration': 26.695, 'max_score': 951.231, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0951231.jpg'}, {'end': 1052.805, 'src': 'embed', 'start': 1018.524, 'weight': 2, 'content': [{'end': 1021.324, 'text': "Now, before we execute that, let's look at the state of the database.", 'start': 1018.524, 'duration': 2.8}, {'end': 1026.965, 'text': 'So laptops quantity available is 10 and we are trying to sell 10 of them.', 'start': 1021.625, 'duration': 5.34}, {'end': 1031.247, 'text': 'So what should happen? They should become 70 and a row should be inserted here.', 'start': 1027.026, 'duration': 4.221}, {'end': 1035.627, 'text': "But we know when it tries to insert a row into TBL product sales table, we'll get an error.", 'start': 1031.666, 'duration': 3.961}, {'end': 1041.089, 'text': 'When that happens, we need to even roll back the update that was done to TBL product table.', 'start': 1035.988, 'duration': 5.101}, {'end': 1042.71, 'text': "Let's see if that happens.", 'start': 1041.69, 'duration': 1.02}, {'end': 1052.805, 'text': 'And just so that we know whether if a statement is committed or rolled back, what we will do is we will wrap this inside a begin and end statement.', 'start': 1043.479, 'duration': 9.326}], 'summary': 'Database update should increase laptops quantity to 70, but may encounter an error and require a rollback.', 'duration': 34.281, 'max_score': 1018.524, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta01018524.jpg'}], 'start': 778.201, 'title': 'Managing sql transactions', 'summary': 'Discusses the importance of managing sql transactions to maintain data consistency, emphasizing the need to rollback the entire transaction if an error occurs and to commit the transaction only if there are no errors, highlighting the use of the at aterror system function for error checking and transaction control.', 'chapters': [{'end': 949.611, 'start': 778.201, 'title': 'Managing transactions in sql server', 'summary': 'Discusses the importance of managing sql transactions to maintain data consistency, emphasizing the need to rollback the entire transaction if an error occurs and to commit the transaction only if there are no errors, highlighting the use of the at aterror system function for error checking and transaction control.', 'duration': 171.41, 'highlights': ['The importance of ensuring data consistency by treating multiple SQL statements as a single transaction is emphasized, with the need for both update and insert statements to either both succeed or both fail, as inconsistency arises when one statement succeeds and the other fails.', 'The significance of error checking in SQL transactions is highlighted, stressing the need to use the at atError system function to check for errors and to rollback the entire transaction if an error occurs, thus preventing incomplete or inconsistent data modifications.', 'The incorrect practice of committing a transaction without checking for errors is pointed out, underscoring the necessity of checking for errors using the at atError system function before committing the transaction to maintain data integrity.', 'The method of using the at atError system function to check for errors in SQL transactions is explained, focusing on how the function returns a non-zero value if an error occurs and 0 if the statement successfully executes, enabling effective error detection and transaction control.']}, {'end': 1160.911, 'start': 951.231, 'title': 'Transaction rollback in sql', 'summary': 'Explains the concept of rolling back transactions in sql, demonstrating how to handle errors and ensure data integrity by rolling back or committing transactions based on error conditions, resulting in a successful commit and rollback of transactions with quantifiable data.', 'duration': 209.68, 'highlights': ['The concept of rolling back transactions in SQL is explained in detail, demonstrating how to handle errors and ensure data integrity by rolling back or committing transactions based on error conditions.', 'The demonstration includes altering stored procedures to sell laptops, checking the state of the database, and executing code to simulate successful and unsuccessful transactions with quantifiable data such as quantity available and transaction outcomes.', 'The process involves checking for error conditions and rolling back the transaction if errors occur, ensuring data integrity and preventing incomplete or erroneous updates to the database.', 'Executing the stored procedure results in either a successful commit or a rollback of the transaction, with quantifiable data showing the impact on the database such as updated quantities and inserted rows in the product sales table.']}], 'duration': 382.71, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta0778201.jpg', 'highlights': ['The importance of ensuring data consistency by treating multiple SQL statements as a single transaction is emphasized, with the need for both update and insert statements to either both succeed or both fail, as inconsistency arises when one statement succeeds and the other fails.', 'The concept of rolling back transactions in SQL is explained in detail, demonstrating how to handle errors and ensure data integrity by rolling back or committing transactions based on error conditions.', 'The demonstration includes altering stored procedures to sell laptops, checking the state of the database, and executing code to simulate successful and unsuccessful transactions with quantifiable data such as quantity available and transaction outcomes.', 'The method of using the at atError system function to check for errors in SQL transactions is explained, focusing on how the function returns a non-zero value if an error occurs and 0 if the statement successfully executes, enabling effective error detection and transaction control.']}, {'end': 1442.73, 'segs': [{'end': 1186.167, 'src': 'embed', 'start': 1160.911, 'weight': 0, 'content': [{'end': 1170.181, 'text': 'we are basically using this system function at at error, but there is a slight, you know,', 'start': 1160.911, 'duration': 9.27}, {'end': 1175.143, 'text': 'thing that you have to keep in mind when you use this at at error function.', 'start': 1170.181, 'duration': 4.962}, {'end': 1181.665, 'text': 'because, look at this, this at at error returns a non-zero value if there is an error, otherwise zero,', 'start': 1175.143, 'duration': 6.522}, {'end': 1186.167, 'text': 'indicating that the previous SQL statement encountered no errors.', 'start': 1181.665, 'duration': 4.502}], 'summary': 'Using at at error function: returns non-zero value for errors, zero for no errors.', 'duration': 25.256, 'max_score': 1160.911, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta01160911.jpg'}, {'end': 1238.692, 'src': 'embed', 'start': 1209.748, 'weight': 3, 'content': [{'end': 1212.089, 'text': 'If you look at the example here, look at this.', 'start': 1209.748, 'duration': 2.341}, {'end': 1214.29, 'text': "Let's say TBL product table.", 'start': 1213.009, 'duration': 1.281}, {'end': 1223.422, 'text': 'If you take TBL product table here, Now we already have a row with id is equal to 2 and product id is the primary key.', 'start': 1214.33, 'duration': 9.092}, {'end': 1230.867, 'text': 'So obviously when we try to insert into that table another product with the same primary key, what we will get? We will get an error.', 'start': 1223.802, 'duration': 7.065}, {'end': 1232.528, 'text': 'Primary key violation error.', 'start': 1231.127, 'duration': 1.401}, {'end': 1234.709, 'text': 'And then here you are checking.', 'start': 1233.128, 'duration': 1.581}, {'end': 1235.45, 'text': 'See, look at this.', 'start': 1234.729, 'duration': 0.721}, {'end': 1238.692, 'text': 'When we execute this insert statement, we get an error.', 'start': 1235.49, 'duration': 3.202}], 'summary': 'Inserting a duplicate product id into tbl product table results in primary key violation error', 'duration': 28.944, 'max_score': 1209.748, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta01209748.jpg'}, {'end': 1276.85, 'src': 'heatmap', 'start': 1251.128, 'weight': 0.807, 'content': [{'end': 1257.232, 'text': 'Now, since we are checking this system function immediately after the statement that caused the error,', 'start': 1251.128, 'duration': 6.104}, {'end': 1261.214, 'text': 'then it retains the error and it shows error occurred.', 'start': 1257.232, 'duration': 3.982}, {'end': 1262.815, 'text': "Let's see that.", 'start': 1262.155, 'duration': 0.66}, {'end': 1266.438, 'text': "So it's the same thing here.", 'start': 1265.197, 'duration': 1.241}, {'end': 1269.6, 'text': "So let's copy this.", 'start': 1266.458, 'duration': 3.142}, {'end': 1275.008, 'text': "So obviously, when we try to execute this, we'll get a primary key violation.", 'start': 1271.465, 'duration': 3.543}, {'end': 1276.85, 'text': 'When we execute that, look at that.', 'start': 1275.449, 'duration': 1.401}], 'summary': 'System function retains error, shows violation upon execution.', 'duration': 25.722, 'max_score': 1251.128, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta01251128.jpg'}, {'end': 1361.143, 'src': 'embed', 'start': 1331.661, 'weight': 1, 'content': [{'end': 1333.502, 'text': 'So the value is reset.', 'start': 1331.661, 'duration': 1.841}, {'end': 1341.444, 'text': "So obviously now when I run this code, it will say no errors and that doesn't make sense.", 'start': 1334.142, 'duration': 7.302}, {'end': 1351.779, 'text': "Right? So that's why anytime you use this edit error, you will have to check the value of that immediately.", 'start': 1342.724, 'duration': 9.055}, {'end': 1357.061, 'text': 'Look at this, edit error is cleared and reset on each statement execution.', 'start': 1351.799, 'duration': 5.262}, {'end': 1361.143, 'text': "Check it immediately following the statement that's being verified,", 'start': 1357.382, 'duration': 3.761}], 'summary': 'Edit error value reset to avoid false positive results.', 'duration': 29.482, 'max_score': 1331.661, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta01331661.jpg'}, {'end': 1435.681, 'src': 'embed', 'start': 1381.719, 'weight': 2, 'content': [{'end': 1387.903, 'text': "immediately after that, you are storing the value that's present in editError into this local variable.", 'start': 1381.719, 'duration': 6.184}, {'end': 1395.807, 'text': 'And then you are executing select star from TBL product, which will only reset this global variable, I mean, this global system function.', 'start': 1388.503, 'duration': 7.304}, {'end': 1405.405, 'text': 'But then this local variable contains the value that was, you know, returned by this editError when this statement, when this insert into statement,', 'start': 1396.468, 'duration': 8.937}, {'end': 1406.125, 'text': 'was executed.', 'start': 1405.405, 'duration': 0.72}, {'end': 1409.306, 'text': 'So at this point of time you will get what you expect.', 'start': 1406.445, 'duration': 2.861}, {'end': 1413.647, 'text': 'So here we are saving the value to a local variable at error.', 'start': 1409.486, 'duration': 4.161}, {'end': 1424.109, 'text': "So obviously now when we execute this, it's the same code that we have seen, it should print that message saying error occurred.", 'start': 1415.667, 'duration': 8.442}, {'end': 1425.77, 'text': 'So error occurred.', 'start': 1424.429, 'duration': 1.341}, {'end': 1435.681, 'text': "Alright, in the next session we'll see how to use try-catch to handle errors in SQL Server 2005 and 2008.", 'start': 1427.87, 'duration': 7.811}], 'summary': 'Storing value from editerror into local variable and executing select star from tbl product resets the global system function, leading to an error message.', 'duration': 53.962, 'max_score': 1381.719, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta01381719.jpg'}], 'start': 1160.911, 'title': 'Handling sql errors', 'summary': 'Covers the usage of at at error function in sql, handling primary key violation errors, and managing edit error in sql code. it provides examples and emphasizes the importance of immediate error checking after statement execution, highlighting the impact on error retention and introducing error handling using try-catch in sql server 2005 and 2008.', 'chapters': [{'end': 1209.307, 'start': 1160.911, 'title': 'Using at at error function in sql', 'summary': 'Explains the usage of the at at error function in sql, which returns a non-zero value if there is an error and is cleared and reset on each statement execution, requiring immediate or later value checking, illustrated with an example.', 'duration': 48.396, 'highlights': ['The at at error function returns a non-zero value if there is an error, otherwise zero, indicating that the previous SQL statement encountered no errors.', 'The at at error is cleared and reset on each statement execution, requiring immediate or later value checking.', 'Saving the at at error value to a local variable allows for checking at a later point of time.']}, {'end': 1307.899, 'start': 1209.748, 'title': 'Handling primary key violation errors', 'summary': 'Discusses how to handle primary key violation errors when inserting data into a table, demonstrating the use of system functions to check for errors immediately after statement execution and the impact on error retention.', 'duration': 98.151, 'highlights': ['The speaker highlights the concept of primary key violation error when attempting to insert a product with the same primary key, emphasizing the occurrence of an error in such a scenario.', "The importance of checking the value of the system function, editError, immediately after the statement execution is highlighted, indicating that a non-zero value signifies an error, leading to the printing of 'error occurred'.", "The impact of not checking the system function value immediately after the statement execution is explained, demonstrating that the error is not retained and 'error occurred' is not printed even though a primary key violation error occurs.", "The speaker demonstrates the impact of not checking the system function value immediately after the statement execution by executing a select statement after the insert statement, showing that the error is not retained and 'error occurred' is not printed."]}, {'end': 1442.73, 'start': 1308.399, 'title': 'Managing edit error in sql code', 'summary': 'Discusses the management of the edit error in sql code, emphasizing the need to check and handle the value of edit error immediately after statement execution to avoid incorrect behavior; it also introduces the option of saving it to a local variable for later inspection, ultimately leading to the understanding of how to handle errors using try-catch in sql server 2005 and 2008.', 'duration': 134.331, 'highlights': ['Immediate checking and handling of edit error value after statement execution is emphasized to avoid incorrect behavior, as it gets reset to zero upon successful execution, ensuring the code accurately reflects error status.', 'The option of saving the edit error value to a local variable for later inspection is introduced, providing a method to capture and verify the error status at a later point in the code execution.', 'The upcoming session will delve into the utilization of try-catch to effectively manage errors in SQL Server 2005 and 2008, offering a comprehensive approach to error handling and resolution in SQL development.']}], 'duration': 281.819, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/xgpyqxKuta0/pics/xgpyqxKuta01160911.jpg', 'highlights': ['The at at error function returns a non-zero value if there is an error, otherwise zero', 'Immediate checking and handling of edit error value after statement execution is emphasized', 'The option of saving the edit error value to a local variable for later inspection is introduced', 'The speaker highlights the concept of primary key violation error when attempting to insert a product with the same primary key', 'The upcoming session will delve into the utilization of try-catch to effectively manage errors in SQL Server 2005 and 2008']}], 'highlights': ['With the introduction of try-catch blocks in SQL Server 2005, error handling is similar to C-sharp and Java.', 'The importance of ensuring data consistency by treating multiple SQL statements as a single transaction is emphasized, with the need for both update and insert statements to either both succeed or both fail, as inconsistency arises when one statement succeeds and the other fails.', 'The concept of rolling back transactions in SQL is explained in detail, demonstrating how to handle errors and ensure data integrity by rolling back or committing transactions based on error conditions.', 'The method of using the at atError system function to check for errors in SQL transactions is explained, focusing on how the function returns a non-zero value if an error occurs and 0 if the statement successfully executes, enabling effective error detection and transaction control.', 'The TBL product table holds product information with product ID as primary key.', 'The stored procedure requires two parameters: product ID and quantity.', 'The process involves checking stock availability for a specific product.', 'The TBL product sales table contains transaction details with product sales ID as primary key.', 'The process of selling a product involves checking available quantity and deducting sold quantity.', 'The severity level in most cases is 16, indicating general errors that can be corrected by the user, and is typically used for returning custom errors.', 'The error state is a number between 1 and 255, but when using raise error for custom exceptions, it can only be a value between 1 and 127, typically being 1.', 'Immediate checking and handling of edit error value after statement execution is emphasized', 'The at at error function returns a non-zero value if there is an error, otherwise zero', 'The speaker highlights the concept of primary key violation error when attempting to insert a product with the same primary key', 'The upcoming session will delve into the utilization of try-catch to effectively manage errors in SQL Server 2005 and 2008', 'System functions starting with at sign are not variables and do not behave like variables.', 'It is strongly recommended to watch part 18 of the video series on stored procedures before proceeding.']}