title
Stored procedures with output parameters Part 19

description
In this video we will learn 1. Creating and executing stored procedures with output parameters 2. Learn about some of the useful system stored procedures Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/stored-procedures-with-output.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/08/part-19-stored-procedures-with-output.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': 'Stored procedures with output parameters Part 19', 'heatmap': [{'end': 254.176, 'start': 239.495, 'weight': 0.715}, {'end': 287.562, 'start': 275.852, 'weight': 0.724}, {'end': 341.165, 'start': 317.741, 'weight': 0.749}, {'end': 451.73, 'start': 426.464, 'weight': 0.878}, {'end': 880.677, 'start': 865.241, 'weight': 1}], 'summary': "Covers creating and executing stored procedures with output parameters, dynamic gender-based employee count retrieval resulting in 7 male employees, significance of using the 'out' keyword, parameter order and naming for flexibility, and sp_help and sp_depends system stored procedures for viewing dependencies and preventing errors.", 'chapters': [{'end': 501.207, 'segs': [{'end': 45, 'src': 'embed', 'start': 0.029, 'weight': 0, 'content': [{'end': 1.889, 'text': 'Hello Welcome to Prejim Technologies.', 'start': 0.029, 'duration': 1.86}, {'end': 2.65, 'text': 'I am Venkat.', 'start': 2.069, 'duration': 0.581}, {'end': 4.95, 'text': 'This is part 19 of SQL Server.', 'start': 2.91, 'duration': 2.04}, {'end': 7.051, 'text': 'In this session,', 'start': 6.11, 'duration': 0.941}, {'end': 14.492, 'text': 'we will learn creating and executing stored procedures with output parameters and learn about some of the useful system stored procedures.', 'start': 7.051, 'duration': 7.441}, {'end': 20.633, 'text': 'Now, before continuing with this session, I strongly recommend to watch part 18 of this video series.', 'start': 14.972, 'duration': 5.661}, {'end': 27.875, 'text': 'In part 18 of this video series, we have seen how to create stored procedures with input parameters.', 'start': 21.974, 'duration': 5.901}, {'end': 32.929, 'text': 'In this session, we will see how to create a stored procedure with output parameter.', 'start': 28.365, 'duration': 4.564}, {'end': 38.414, 'text': 'To create a stored procedure with output parameter, we use the keywords OUT and OUTPUT.', 'start': 33.329, 'duration': 5.085}, {'end': 45, 'text': 'If you look at the stored procedure that we have here SP GET EMPLOYEE COUNT BY GENDER.', 'start': 38.894, 'duration': 6.106}], 'summary': 'Learn about creating and executing stored procedures with output parameters in part 19 of sql server.', 'duration': 44.971, 'max_score': 0.029, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk29.jpg'}, {'end': 219.862, 'src': 'embed', 'start': 198.851, 'weight': 1, 'content': [{'end': 207.895, 'text': 'we want the total number of you know, male or female employees, depending on what gets passed into the stored procedure.', 'start': 198.851, 'duration': 9.044}, {'end': 209.516, 'text': 'so where gender is equal to now?', 'start': 207.895, 'duration': 1.621}, {'end': 216.1, 'text': 'if you, if I say gender is equal to male, then it will give us the total number of male employees, which is 7.', 'start': 209.516, 'duration': 6.584}, {'end': 219.862, 'text': "but we don't want to be hard coding the gender like this.", 'start': 216.1, 'duration': 3.762}], 'summary': 'Retrieve total number of male employees (7) without hardcoding gender.', 'duration': 21.011, 'max_score': 198.851, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk198851.jpg'}, {'end': 271.029, 'src': 'heatmap', 'start': 239.495, 'weight': 0.715, 'content': [{'end': 242.958, 'text': 'instead we want to initialize this output parameter.', 'start': 239.495, 'duration': 3.463}, {'end': 248.903, 'text': "so that's why we will say select at employee count is equal to.", 'start': 242.958, 'duration': 5.945}, {'end': 250.004, 'text': "so what's going to happen?", 'start': 248.903, 'duration': 1.101}, {'end': 254.176, 'text': 'whatever we get, I mean whatever this count function returns.', 'start': 250.004, 'duration': 4.172}, {'end': 259.54, 'text': 'instead of selecting it as a result set, we are passing it to the output variable.', 'start': 254.176, 'duration': 5.364}, {'end': 263.863, 'text': 'so the output parameter gets initialized.', 'start': 259.54, 'duration': 4.323}, {'end': 271.029, 'text': "And instead of saying star, it's always good to use the column name there.", 'start': 265.685, 'duration': 5.344}], 'summary': 'Initializing output parameter with employee count for better efficiency.', 'duration': 31.534, 'max_score': 239.495, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk239495.jpg'}, {'end': 308.738, 'src': 'heatmap', 'start': 275.852, 'weight': 0.724, 'content': [{'end': 277.313, 'text': "So let's create the stored procedure.", 'start': 275.852, 'duration': 1.461}, {'end': 280.816, 'text': 'So we have successfully created a stored procedure.', 'start': 278.494, 'duration': 2.322}, {'end': 282.937, 'text': 'with an output parameter.', 'start': 281.195, 'duration': 1.742}, {'end': 287.562, 'text': 'all right now, to execute the stored procedure with output parameter.', 'start': 282.937, 'duration': 4.625}, {'end': 289.605, 'text': 'now we have created the stored procedure.', 'start': 287.562, 'duration': 2.043}, {'end': 291.326, 'text': 'we want to execute that.', 'start': 289.605, 'duration': 1.721}, {'end': 297.513, 'text': 'okay now, usually we have seen how to execute a stored procedure with input parameters in the previous session.', 'start': 291.326, 'duration': 6.187}, {'end': 300.497, 'text': 'okay, to execute a stored procedure with output parameter.', 'start': 297.513, 'duration': 2.984}, {'end': 302.996, 'text': "it's slightly different.", 'start': 301.115, 'duration': 1.881}, {'end': 308.738, 'text': 'okay now, you know that this stored procedure is accepting two parameters.', 'start': 302.996, 'duration': 5.742}], 'summary': 'Successfully created a stored procedure with an output parameter, and learned to execute it.', 'duration': 32.886, 'max_score': 275.852, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk275852.jpg'}, {'end': 345.386, 'src': 'heatmap', 'start': 301.115, 'weight': 3, 'content': [{'end': 302.996, 'text': "it's slightly different.", 'start': 301.115, 'duration': 1.881}, {'end': 308.738, 'text': 'okay now, you know that this stored procedure is accepting two parameters.', 'start': 302.996, 'duration': 5.742}, {'end': 313.34, 'text': 'one is the gender parameter and the other one is the employee count.', 'start': 308.738, 'duration': 4.602}, {'end': 315.06, 'text': 'gender is an input parameter.', 'start': 313.34, 'duration': 1.72}, {'end': 317.741, 'text': 'employee count is an output parameter.', 'start': 315.06, 'duration': 2.681}, {'end': 324.164, 'text': 'okay now, obviously, input parameter means you have to pass something to the stored procedure,', 'start': 317.741, 'duration': 6.423}, {'end': 328.145, 'text': 'whereas output parameter will return something back to you.', 'start': 324.164, 'duration': 3.981}, {'end': 336.921, 'text': 'okay, now, when a stored procedure return something back to you, you want to hold it somewhere, obviously in a variable.', 'start': 328.145, 'duration': 8.776}, {'end': 341.165, 'text': "that's why you first create a variable to receive the value.", 'start': 336.921, 'duration': 4.244}, {'end': 345.386, 'text': "Okay, so I'm creating a variable called at employee total.", 'start': 342.185, 'duration': 3.201}], 'summary': 'The stored procedure accepts gender and employee count as parameters, with employee count as an output parameter which needs to be held in a variable called employee total.', 'duration': 44.271, 'max_score': 301.115, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk301115.jpg'}, {'end': 396.393, 'src': 'embed', 'start': 371.075, 'weight': 2, 'content': [{'end': 377.34, 'text': 'we are executing the stored procedure here we pass the value for gender, which is nothing but the male.', 'start': 371.075, 'duration': 6.265}, {'end': 379.241, 'text': 'and then what is this query going to do??', 'start': 377.34, 'duration': 1.901}, {'end': 384.104, 'text': "It's going to count the number of males and then it's going to initialize.", 'start': 379.301, 'duration': 4.803}, {'end': 386.006, 'text': 'you know this output parameter?', 'start': 384.104, 'duration': 1.902}, {'end': 392.47, 'text': 'Okay, so we are passing in a variable, which is going to receive that count.', 'start': 386.846, 'duration': 5.624}, {'end': 396.393, 'text': 'In this case, at employee total receives the value.', 'start': 392.911, 'duration': 3.482}], 'summary': 'Executing stored procedure to count male employees and initialize output parameter.', 'duration': 25.318, 'max_score': 371.075, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk371075.jpg'}, {'end': 463.938, 'src': 'heatmap', 'start': 426.464, 'weight': 0.878, 'content': [{'end': 431.008, 'text': 'Which stored procedure is that? SP employee get count by gender.', 'start': 426.464, 'duration': 4.544}, {'end': 438.334, 'text': 'And then when you execute look at this when you execute that stored procedure, obviously you have to pass in,', 'start': 431.889, 'duration': 6.445}, {'end': 441.997, 'text': 'for you know which gender do you want to count?', 'start': 438.334, 'duration': 3.663}, {'end': 443.779, 'text': 'the number of employees, male or female?', 'start': 441.997, 'duration': 1.782}, {'end': 446.541, 'text': 'You have to pass that input value for that input parameter.', 'start': 443.799, 'duration': 2.742}, {'end': 449.908, 'text': "But whereas for the output parameter, you don't have to pass a value.", 'start': 446.906, 'duration': 3.002}, {'end': 451.73, 'text': 'You will get a value back.', 'start': 450.248, 'duration': 1.482}, {'end': 463.938, 'text': 'So, here we want to check the count of male employees and then we pass this variable to receive the value back.', 'start': 452.45, 'duration': 11.488}], 'summary': 'A stored procedure is used to get count by gender, with input and output parameters for employee count.', 'duration': 37.474, 'max_score': 426.464, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk426464.jpg'}], 'start': 0.029, 'title': 'Stored procedures and output handling', 'summary': 'Covers creating and executing stored procedures with output parameters, learning about system stored procedures, and handling the output of a stored procedure. it emphasizes dynamic gender-based employee count retrieval and demonstrates the creation of a stored procedure resulting in a count of 7 male employees.', 'chapters': [{'end': 140.831, 'start': 0.029, 'title': 'Sql server part 19: stored procedures and system stored procedures', 'summary': 'Covers creating and executing stored procedures with output parameters, and learning about useful system stored procedures, emphasizing the importance of watching part 18, and demonstrating how to create a stored procedure with output parameters.', 'duration': 140.802, 'highlights': ['The importance of watching part 18 of the video series before continuing with this session', 'Demonstrating the creation of a stored procedure with output parameter using keywords OUT and OUTPUT', 'Explanation of how to create a stored procedure to retrieve employee count by gender and the usage of output parameters', 'Emphasizing the usage of meaningful names for stored procedures and the command to create a stored procedure']}, {'end': 328.145, 'start': 140.831, 'title': 'Stored procedure with output parameter', 'summary': 'Covers the creation and execution of a stored procedure with an output parameter, explaining the usage of input and output parameters and their distinction, emphasizing the need for dynamic gender-based employee count retrieval.', 'duration': 187.314, 'highlights': ['The chapter covers the creation and execution of a stored procedure with an output parameter It provides an overview of the main focus of the discussion, highlighting the central theme of the transcript.', 'Emphasizing the need for dynamic gender-based employee count retrieval The transcript emphasizes the importance of retrieving gender-based employee counts dynamically, rather than hard-coding values, to enhance the flexibility and applicability of the stored procedure.', 'Explanation of the usage of input and output parameters and their distinction It clarifies the roles of input and output parameters in stored procedures, highlighting the difference between them and their respective purposes in data manipulation and retrieval.']}, {'end': 501.207, 'start': 328.145, 'title': 'Stored procedure output handling', 'summary': 'Explains how to handle the output of a stored procedure, including creating a variable to hold the output, executing the stored procedure, and specifying the output parameter type, with an example resulting in a count of 7 male employees.', 'duration': 173.062, 'highlights': ['Executing a stored procedure involves creating a variable to hold the output, matching its data type with the output parameter, and then executing the stored procedure with specified input values. Creating a variable to hold the output, matching data types, executing the stored procedure.', "When executing the stored procedure 'SP employee get count by gender' with input value 'male', the result is a count of 7 male employees stored in the variable 'at employee total'. Executing the stored procedure with 'male' as input, resulting in a count of 7 male employees stored in variable 'at employee total'.", "To handle the output parameter, it's necessary to specify the output keyword to indicate it as an output parameter, which then allows the stored procedure to return the value back. Specifying the output keyword for the output parameter to enable the stored procedure to return the value back."]}], 'duration': 501.178, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk29.jpg', 'highlights': ['Demonstrating the creation of a stored procedure with output parameter using keywords OUT and OUTPUT', 'Emphasizing the need for dynamic gender-based employee count retrieval', "Executing the stored procedure with 'male' as input, resulting in a count of 7 male employees stored in variable 'at employee total'", 'Explanation of the usage of input and output parameters and their distinction', 'The importance of watching part 18 of the video series before continuing with this session']}, {'end': 716.34, 'segs': [{'end': 634.727, 'src': 'embed', 'start': 608.717, 'weight': 0, 'content': [{'end': 613.879, 'text': 'alright, so keep that in mind whenever you execute a stored procedure with output parameter.', 'start': 608.717, 'duration': 5.162}, {'end': 623.443, 'text': 'you know, for that output parameter you have to specify the out keyword, otherwise it will not be initialized and it will always stay null.', 'start': 613.879, 'duration': 9.564}, {'end': 633.287, 'text': "alright, okay, so if you don't specify the output keyword when executing the stored procedure, the the at employee total variable will be null,", 'start': 623.443, 'duration': 9.844}, {'end': 634.727, 'text': 'and we have just seen that.', 'start': 633.287, 'duration': 1.44}], 'summary': 'When executing a stored procedure, specifying the output keyword for output parameters is essential to avoid null values.', 'duration': 26.01, 'max_score': 608.717, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk608717.jpg'}, {'end': 684.994, 'src': 'embed', 'start': 658.775, 'weight': 2, 'content': [{'end': 665.34, 'text': "If you use the parameter names when you're passing values to the stored procedure, then the order doesn't really matter.", 'start': 658.775, 'duration': 6.565}, {'end': 666.921, 'text': 'For example, look at this.', 'start': 665.5, 'duration': 1.421}, {'end': 670.083, 'text': "Let's say I want to pass my output parameter first.", 'start': 667.201, 'duration': 2.882}, {'end': 673.406, 'text': 'How do I do that? You have to use the parameter names.', 'start': 670.163, 'duration': 3.243}, {'end': 676.088, 'text': "Let's get rid of this here.", 'start': 674.326, 'duration': 1.762}, {'end': 684.994, 'text': "So what's the parameter name? It's atEmployeeCount.", 'start': 680.651, 'duration': 4.343}], 'summary': 'Using parameter names for passing values to stored procedure makes order irrelevant.', 'duration': 26.219, 'max_score': 658.775, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk658775.jpg'}], 'start': 501.507, 'title': 'Stored procedure best practices', 'summary': "Discusses the significance of using the 'out' keyword when executing a stored procedure and the importance of parameter order and naming for flexibility and correctness. it emphasizes the impact of not using the 'out' keyword and provides an example of altering parameter order while specifying names.", 'chapters': [{'end': 633.287, 'start': 501.507, 'title': 'Stored procedure output keyword', 'summary': "Discusses the importance of specifying the 'out' keyword when executing a stored procedure to initialize the output parameter, highlighting the consequences of not doing so and the impact on the variable's value.", 'duration': 131.78, 'highlights': ["When executing a stored procedure without specifying the 'out' keyword for the output parameter, the variable will not be initialized and will remain null, leading to unexpected behavior.", "If the 'out' keyword is not specified, the variable will not receive a value, resulting in it staying null, as demonstrated by the example.", "Emphasizing the need to specify the 'out' keyword for output parameters in stored procedures to ensure proper initialization and avoid null values."]}, {'end': 716.34, 'start': 633.287, 'title': 'Stored procedure parameter passing', 'summary': 'Discusses the importance of parameter order in stored procedures and demonstrates how using parameter names when passing values allows for flexibility and ensures the correct values are assigned, as shown by the example of altering the parameter order while specifying parameter names.', 'duration': 83.053, 'highlights': ['Using parameter names when passing values to a stored procedure ensures the correct values are assigned, regardless of the order of the parameters, providing flexibility and clarity (e.g., atEmployeeCount and at gender).', 'Demonstration of altering the order of parameters while specifying parameter names to show how the stored procedure can still correctly assign the values to the respective parameters.']}], 'duration': 214.833, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk501507.jpg', 'highlights': ["When executing a stored procedure without specifying the 'out' keyword for the output parameter, the variable will not be initialized and will remain null, leading to unexpected behavior.", "Emphasizing the need to specify the 'out' keyword for output parameters in stored procedures to ensure proper initialization and avoid null values.", 'Using parameter names when passing values to a stored procedure ensures the correct values are assigned, regardless of the order of the parameters, providing flexibility and clarity (e.g., atEmployeeCount and at gender).', 'Demonstration of altering the order of parameters while specifying parameter names to show how the stored procedure can still correctly assign the values to the respective parameters.']}, {'end': 1098.731, 'segs': [{'end': 858.037, 'src': 'embed', 'start': 804.754, 'weight': 0, 'content': [{'end': 808.835, 'text': 'And if you expand that, you should see the parameters that it is expecting.', 'start': 804.754, 'duration': 4.081}, {'end': 811.976, 'text': 'All right.', 'start': 811.656, 'duration': 0.32}, {'end': 825.613, 'text': 'Okay, now this SP underscore help, you know, you can use that with any database object, for example, tables, views, triggers, etc.', 'start': 816.741, 'duration': 8.872}, {'end': 833.645, 'text': 'For example, look at this when I use this SP underscore help with this TBL employee, what information do I get?', 'start': 825.954, 'duration': 7.691}, {'end': 840.972, 'text': 'I get all the information about the different columns that are present in this table, their data types, etc.', 'start': 834.389, 'duration': 6.583}, {'end': 845.914, 'text': ', any indexes this table has got, any constraints this table has got.', 'start': 840.972, 'duration': 4.942}, {'end': 849.055, 'text': 'Basically, you get the information about the object.', 'start': 845.954, 'duration': 3.101}, {'end': 853.854, 'text': 'It could be table, view, stored procedure, trigger, etc.', 'start': 850.532, 'duration': 3.322}, {'end': 858.037, 'text': 'So sp underscore help work with any database object.', 'start': 854.154, 'duration': 3.883}], 'summary': 'Using sp_help provides detailed information about database objects, including tables, views, and triggers.', 'duration': 53.283, 'max_score': 804.754, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk804754.jpg'}, {'end': 893.285, 'src': 'heatmap', 'start': 865.241, 'weight': 1, 'content': [{'end': 871.465, 'text': 'Or what you can do is simply highlight that object and press Alt F1.', 'start': 865.241, 'duration': 6.224}, {'end': 875.727, 'text': "That's a keyboard shortcut and you get the exact same information again.", 'start': 871.525, 'duration': 4.202}, {'end': 880.677, 'text': "Okay So that's about SP underscore help system stored procedure.", 'start': 876.688, 'duration': 3.989}, {'end': 886.941, 'text': 'And in part 18 of this video series, we have seen how, you know, what is the purpose of SP underscore help text.', 'start': 881.017, 'duration': 5.924}, {'end': 893.285, 'text': 'If you want to view the definition of a stored procedure, we use SP underscore help text system stored procedure.', 'start': 887.462, 'duration': 5.823}], 'summary': 'Keyboard shortcut alt f1 retrieves same info about sp_help system stored procedure.', 'duration': 28.044, 'max_score': 865.241, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk865241.jpg'}, {'end': 906.957, 'src': 'embed', 'start': 881.017, 'weight': 1, 'content': [{'end': 886.941, 'text': 'And in part 18 of this video series, we have seen how, you know, what is the purpose of SP underscore help text.', 'start': 881.017, 'duration': 5.924}, {'end': 893.285, 'text': 'If you want to view the definition of a stored procedure, we use SP underscore help text system stored procedure.', 'start': 887.462, 'duration': 5.823}, {'end': 902.071, 'text': 'Okay Similarly, even if it is a stored procedure with output parameters, you can still use SP underscore help text system stored procedure.', 'start': 893.686, 'duration': 8.385}, {'end': 904.195, 'text': 'Okay, press F5.', 'start': 902.091, 'duration': 2.104}, {'end': 906.957, 'text': 'you should see the text of the system stored procedure.', 'start': 904.195, 'duration': 2.762}], 'summary': 'Part 18 explains the purpose of sp_help_text for viewing stored procedure definitions.', 'duration': 25.94, 'max_score': 881.017, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk881017.jpg'}, {'end': 951.516, 'src': 'embed', 'start': 924.911, 'weight': 2, 'content': [{'end': 930.435, 'text': 'okay, this stored procedure basically is used to view the dependencies of the stored procedure.', 'start': 924.911, 'duration': 5.524}, {'end': 935.13, 'text': 'For example, if a stored procedure depends on a table, you know, look at this.', 'start': 930.748, 'duration': 4.382}, {'end': 940.572, 'text': 'This stored procedure depends.', 'start': 935.51, 'duration': 5.062}, {'end': 947.875, 'text': 'So this stored procedure, where are we getting the information from? This stored procedure is getting the information from tblEmployee table.', 'start': 940.912, 'duration': 6.963}, {'end': 951.516, 'text': 'So this stored procedure depends on this table.', 'start': 948.195, 'duration': 3.321}], 'summary': 'Stored procedure views dependencies on tblemployee table.', 'duration': 26.605, 'max_score': 924.911, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk924911.jpg'}, {'end': 1098.731, 'src': 'embed', 'start': 1075.346, 'weight': 3, 'content': [{'end': 1077.286, 'text': 'This system stored procedure is very useful,', 'start': 1075.346, 'duration': 1.94}, {'end': 1084.968, 'text': 'especially if you want to check if there are any stored procedures that are referencing a table that you are about to drop.', 'start': 1077.286, 'duration': 7.682}, {'end': 1089.829, 'text': 'SP underscore depends can also be used with other database objects like tables, etc.', 'start': 1085.548, 'duration': 4.281}, {'end': 1095.65, 'text': 'On this slide, you can find resources for ASP.NET and C Sharp interview questions.', 'start': 1092.21, 'duration': 3.44}, {'end': 1096.851, 'text': "That's it for today.", 'start': 1096.07, 'duration': 0.781}, {'end': 1097.811, 'text': 'Thank you for listening.', 'start': 1097.011, 'duration': 0.8}, {'end': 1098.731, 'text': 'Have a great day.', 'start': 1098.111, 'duration': 0.62}], 'summary': 'System stored procedure sp_depends checks for references before dropping a table.', 'duration': 23.385, 'max_score': 1075.346, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk1075346.jpg'}], 'start': 717.28, 'title': 'Sp_help and sp_depends', 'summary': 'Introduces sp_help and sp_helptext system stored procedures providing information about database objects, and explains sp_depends for viewing dependencies, preventing potential errors.', 'chapters': [{'end': 924.911, 'start': 717.28, 'title': 'Understanding sp_help system stored procedure', 'summary': 'Introduces the sp_help system stored procedure, which provides information about stored procedures, tables, views, and triggers, and also explains the purpose of sp_helptext and sp_depends.', 'duration': 207.631, 'highlights': ['SP_Help provides information about different database objects like stored procedures, tables, views, and triggers, including details such as parameter names, data types, indexes, and constraints. SP_Help can be used to retrieve comprehensive information about various database objects, aiding in understanding their structure and properties.', 'SP_HelpText is used to view the definition of a stored procedure, even with output parameters, and it cannot display the text of encrypted stored procedures. SP_HelpText allows users to access the text of stored procedures and understand their structure, but it cannot decrypt encrypted stored procedures.', 'SP_Depends is highlighted as a useful system stored procedure without further details provided in the transcript. SP_Depends is mentioned as a useful system stored procedure, but specific information about its functionality is not provided in the transcript.']}, {'end': 1098.731, 'start': 924.911, 'title': 'View stored procedure dependencies', 'summary': 'Explains the sp_depends stored procedure, which helps in viewing dependencies of a stored procedure, enabling users to identify if any other database objects are referencing a table or stored procedure that they intend to drop, preventing potential errors or issues.', 'duration': 173.82, 'highlights': ['The SP_depends stored procedure helps in identifying dependencies of a stored procedure, such as tables and columns. This highlights the main purpose of the SP_depends stored procedure, emphasizing its role in identifying dependencies within the database.', 'It allows users to prevent errors by identifying if a stored procedure is referencing a table that is about to be dropped. This highlights the practical application of SP_depends in preventing errors when attempting to drop a table that is referenced by a stored procedure.', 'The SP_depends stored procedure can also be used with other database objects like tables. This provides additional information about the versatility of the SP_depends stored procedure, indicating that it can be used with various database objects.']}], 'duration': 381.451, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/bldBshxuhMk/pics/bldBshxuhMk717280.jpg', 'highlights': ['SP_Help provides comprehensive information about database objects, aiding in understanding their structure and properties.', 'SP_HelpText allows users to access the text of stored procedures and understand their structure.', 'SP_depends helps in identifying dependencies of a stored procedure, such as tables and columns.', 'It allows users to prevent errors by identifying if a stored procedure is referencing a table that is about to be dropped.', 'The SP_depends stored procedure can also be used with other database objects like tables.']}], 'highlights': ['Demonstrating the creation of a stored procedure with output parameter using keywords OUT and OUTPUT', 'Emphasizing the need for dynamic gender-based employee count retrieval', "Executing the stored procedure with 'male' as input, resulting in a count of 7 male employees stored in variable 'at employee total'", "Emphasizing the need to specify the 'out' keyword for output parameters in stored procedures to ensure proper initialization and avoid null values", 'Using parameter names when passing values to a stored procedure ensures the correct values are assigned, regardless of the order of the parameters, providing flexibility and clarity (e.g., atEmployeeCount and at gender)', 'SP_Help provides comprehensive information about database objects, aiding in understanding their structure and properties', 'SP_HelpText allows users to access the text of stored procedures and understand their structure', 'SP_depends helps in identifying dependencies of a stored procedure, such as tables and columns', 'It allows users to prevent errors by identifying if a stored procedure is referencing a table that is about to be dropped', 'The SP_depends stored procedure can also be used with other database objects like tables']}