title
Stored procedures in sql server Part 18

description
In this video we will learn 1. What is a stored procedure 2. Stored Procedure example 3. Creating a stored procedure with parameters 4. Altering SP 5. Viewing the text of the SP 6. Dropping the SP 7. Encrypting stored procedure Text version of the video http://csharp-video-tutorials.blogspot.com/2012/08/stored-procedures-part-18.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-18-stored-procedures.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 in sql server Part 18', 'heatmap': [{'end': 206.122, 'start': 190.792, 'weight': 0.716}, {'end': 333.32, 'start': 280.303, 'weight': 0.88}, {'end': 440.065, 'start': 406.256, 'weight': 0.787}, {'end': 791.818, 'start': 757.978, 'weight': 0.875}, {'end': 869.288, 'start': 844.454, 'weight': 0.937}], 'summary': 'Series on sql stored procedures covers their definition, benefits, creation, execution, and management, emphasizing time and effort savings, security, parameter usage, best practices, encryption, and upcoming sessions on output parameters and related resources.', 'chapters': [{'end': 222.738, 'segs': [{'end': 48.246, 'src': 'embed', 'start': 0.349, 'weight': 0, 'content': [{'end': 1.61, 'text': 'Welcome to Prejim Technologies.', 'start': 0.349, 'duration': 1.261}, {'end': 2.371, 'text': 'I am Venkat.', 'start': 1.79, 'duration': 0.581}, {'end': 4.693, 'text': 'This is part 18 of SQL Server.', 'start': 2.611, 'duration': 2.082}, {'end': 13.84, 'text': "In this session we'll understand what a stored procedure is a simple stored procedure example, creating a stored procedure with parameters,", 'start': 5.333, 'duration': 8.507}, {'end': 20.306, 'text': "altering a stored procedure, viewing the text of a stored procedure and finally, we'll see how to drop a stored procedure.", 'start': 13.84, 'duration': 6.466}, {'end': 25.394, 'text': 'A stored procedure is a group of Transact SQL statements.', 'start': 22.111, 'duration': 3.283}, {'end': 31.259, 'text': 'If you ever have a situation where you have to write the same query over and over again,', 'start': 25.895, 'duration': 5.364}, {'end': 36.604, 'text': 'you can save that specific query as a stored procedure and call it just by its name.', 'start': 31.259, 'duration': 5.345}, {'end': 39.707, 'text': "Let's understand what we mean by this with an example.", 'start': 36.944, 'duration': 2.763}, {'end': 48.246, 'text': 'Now, I have this table called tblEmployee, which has got the ID, name, gender, and department ID columns.', 'start': 41.462, 'duration': 6.784}], 'summary': 'Venkat from prejim technologies discusses sql server stored procedures and their benefits.', 'duration': 47.897, 'max_score': 0.349, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g349.jpg'}, {'end': 135.456, 'src': 'embed', 'start': 76.404, 'weight': 3, 'content': [{'end': 82.769, 'text': 'So how do we create a stored procedure? To create a stored procedure, we use create procedure command.', 'start': 76.404, 'duration': 6.365}, {'end': 88.774, 'text': 'so create procedure and then you have to give this procedure a name.', 'start': 83.951, 'duration': 4.823}, {'end': 90.675, 'text': 'okay, so SP.', 'start': 88.774, 'duration': 1.901}, {'end': 92.797, 'text': "let's say get employees.", 'start': 90.675, 'duration': 2.122}, {'end': 99.421, 'text': "okay, since this procedure is getting us, you know the employee name and gender, I'm giving it.", 'start': 92.797, 'duration': 6.624}, {'end': 101.202, 'text': 'get employees and look at this.', 'start': 99.421, 'duration': 1.781}, {'end': 101.682, 'text': 'in the name.', 'start': 101.202, 'duration': 0.48}, {'end': 103.764, 'text': 'I have this letters sp.', 'start': 101.682, 'duration': 2.082}, {'end': 112.852, 'text': 'A common naming convention for stored procedures is that we usually prefix that with small letter s and small letter p, indicating that, you know,', 'start': 104.064, 'duration': 8.788}, {'end': 116.996, 'text': 'just by looking at this name you can tell okay, this is a stored procedure.', 'start': 112.852, 'duration': 4.144}, {'end': 126.484, 'text': 'All right, so create procedure, procedure name, and then you will use as begin and end.', 'start': 117.696, 'duration': 8.788}, {'end': 135.456, 'text': 'So the definition of your stored procedure goes between this begin and end.', 'start': 130.09, 'duration': 5.366}], 'summary': "Creating a stored procedure involves using the create procedure command and following a naming convention like 'sp_' to indicate it as a stored procedure.", 'duration': 59.052, 'max_score': 76.404, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g76404.jpg'}, {'end': 217.593, 'src': 'heatmap', 'start': 190.792, 'weight': 0.716, 'content': [{'end': 194.576, 'text': 'Okay, so, if you want to execute, you just need the name of the procedure.', 'start': 190.792, 'duration': 3.784}, {'end': 201.703, 'text': 'So what happens when I execute the stored procedure? Okay, to execute the stored procedure, you just highlight that and click execute.', 'start': 194.876, 'duration': 6.827}, {'end': 203.599, 'text': 'and you get the name and gender.', 'start': 202.318, 'duration': 1.281}, {'end': 206.122, 'text': "You don't have to write that query anymore.", 'start': 203.619, 'duration': 2.503}, {'end': 208.784, 'text': "Now, you might be wondering, it's a very simple query.", 'start': 206.382, 'duration': 2.402}, {'end': 214.81, 'text': "Why don't I write that rather than having to create this procedure and then invoke it? Now, this procedure may be simple.", 'start': 208.804, 'duration': 6.006}, {'end': 217.593, 'text': 'In reality, the procedures will be long.', 'start': 215.21, 'duration': 2.383}], 'summary': 'Executing stored procedure simplifies querying, saving time and effort.', 'duration': 26.801, 'max_score': 190.792, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g190792.jpg'}], 'start': 0.349, 'title': 'Sql stored procedures', 'summary': 'Introduces sql stored procedures, covering their definition, purpose, benefits, and the process of creating, altering, viewing, and dropping them. it also discusses naming conventions, structure, and the simplification of queries, highlighting potential time and effort savings.', 'chapters': [{'end': 75.884, 'start': 0.349, 'title': 'Introduction to sql stored procedures', 'summary': 'Provides an introduction to sql stored procedures, covering the definition of stored procedures, the purpose they serve, and the benefits of using them, along with a simple example and the process of creating, altering, viewing, and dropping stored procedures.', 'duration': 75.535, 'highlights': ['A stored procedure is a group of Transact SQL statements, providing the ability to save and reuse queries, enhancing efficiency and reducing redundancy. A stored procedure allows the saving and reuse of Transact SQL statements, helping in avoiding repetitive query writing and improving efficiency.', 'Demonstrates the process of creating, altering, viewing, and dropping stored procedures, illustrating practical implementation and usage of stored procedures. The session covers the practical aspects of creating, altering, viewing, and dropping stored procedures, providing hands-on guidance for their usage.', 'Illustrates the concept of using stored procedures with a specific example involving the table tblEmployee and the columns ID, name, gender, and department ID, demonstrating the practical application of stored procedures. A specific example involving the table tblEmployee and its columns is used to demonstrate the practical application of using stored procedures, emphasizing their real-world relevance.']}, {'end': 179.885, 'start': 76.404, 'title': 'Creating stored procedures', 'summary': "Discusses the process of creating a stored procedure, emphasizing the use of 'create procedure' command, naming conventions, and the structure of the procedure. it also mentions the steps to verify the creation of the stored procedure.", 'duration': 103.481, 'highlights': ["The stored procedure is created using the 'create procedure' command, followed by a specified name, such as 'SP.get employees', to define its functionality and purpose.", "A naming convention for stored procedures involves prefixing the name with 'sp' (small letter s and p) to indicate it as a stored procedure, facilitating easy identification.", "The body of the stored procedure is enclosed between 'as begin' and 'end', containing the definition of the procedure's functionality and operations.", "To verify the creation of the stored procedure, one needs to navigate to the 'stored procedures' folder within the designated database, such as 'sample', to confirm its existence."]}, {'end': 222.738, 'start': 179.885, 'title': 'Stored procedures simplify queries', 'summary': 'Focuses on the simplification of queries using stored procedures, where instead of writing queries repeatedly, stored procedures can be executed to retrieve information, saving time and effort. it highlights the advantage of executing stored procedures and emphasizes the potential complexity of procedures, with some containing over 3,000 lines.', 'duration': 42.853, 'highlights': ['Stored procedures can simplify query execution by avoiding the need to repeatedly write the same query, saving time and effort.', 'Executing stored procedures retrieves the required information, such as the name and gender of an employee, without the need to rewrite the query.', 'Stored procedures can be advantageous despite the simplicity of the initial query, as they can significantly reduce the effort required for query execution.', 'Emphasizes the potential complexity of procedures, with some containing over 3,000 lines, highlighting the scalability and versatility of stored procedures.']}], 'duration': 222.389, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g349.jpg', 'highlights': ['A stored procedure is a group of Transact SQL statements, providing the ability to save and reuse queries, enhancing efficiency and reducing redundancy.', 'Stored procedures can simplify query execution by avoiding the need to repeatedly write the same query, saving time and effort.', 'Demonstrates the process of creating, altering, viewing, and dropping stored procedures, illustrating practical implementation and usage of stored procedures.', "The stored procedure is created using the 'create procedure' command, followed by a specified name, such as 'SP.get employees', to define its functionality and purpose.", "A naming convention for stored procedures involves prefixing the name with 'sp' (small letter s and p) to indicate it as a stored procedure, facilitating easy identification.", 'Emphasizes the potential complexity of procedures, with some containing over 3,000 lines, highlighting the scalability and versatility of stored procedures.']}, {'end': 637.792, 'segs': [{'end': 250.988, 'src': 'embed', 'start': 223.258, 'weight': 0, 'content': [{'end': 231.241, 'text': 'Okay, and not only that, there are several other benefits of using stored procedures from security to network, reducing network traffic, etc.', 'start': 223.258, 'duration': 7.983}, {'end': 236.803, 'text': 'We will be talking about the advantages of stored procedures in a very great detail in a later session.', 'start': 231.641, 'duration': 5.162}, {'end': 243.486, 'text': 'Okay, so we use create procedure or create proc statement to create SP.', 'start': 238.824, 'duration': 4.662}, {'end': 250.988, 'text': 'I mean, you can either say create procedure or you can just say create proc for shortcut.', 'start': 243.726, 'duration': 7.262}], 'summary': "Stored procedures offer benefits like security, network traffic reduction. use 'create procedure' or 'create proc' for shortcut.", 'duration': 27.73, 'max_score': 223.258, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g223258.jpg'}, {'end': 376.769, 'src': 'heatmap', 'start': 280.303, 'weight': 1, 'content': [{'end': 291.769, 'text': 'or you can use the exec keyword and then click this, or you can use full execute keyword and again press F5,', 'start': 280.303, 'duration': 11.466}, {'end': 294.49, 'text': 'or you can also graphically execute the stored procedure.', 'start': 291.769, 'duration': 2.721}, {'end': 301.134, 'text': 'just right click on the stored procedure and select execute stored procedure, and the moment you do that, it shows this window.', 'start': 294.49, 'duration': 6.644}, {'end': 304.457, 'text': "This procedure doesn't have any parameters.", 'start': 301.854, 'duration': 2.603}, {'end': 307.461, 'text': 'Otherwise, you will have to supply the values for the parameters.', 'start': 304.517, 'duration': 2.944}, {'end': 311.225, 'text': 'In just a bit, we will see how to create a stored procedure that takes parameters.', 'start': 307.861, 'duration': 3.364}, {'end': 315.13, 'text': 'Now, when I click OK, it executes that stored procedure.', 'start': 311.606, 'duration': 3.524}, {'end': 316.051, 'text': 'Look at that.', 'start': 315.55, 'duration': 0.501}, {'end': 317.973, 'text': 'All right.', 'start': 317.653, 'duration': 0.32}, {'end': 321.057, 'text': 'So those are the different ways to execute stored procedure.', 'start': 318.754, 'duration': 2.303}, {'end': 333.32, 'text': 'Now, let us look at a simple example of how to create a stored procedure with parameters.', 'start': 326.71, 'duration': 6.61}, {'end': 342.473, 'text': "Okay, so let's go back to that table, tblEmployees.", 'start': 333.34, 'duration': 9.133}, {'end': 352.843, 'text': 'All right, now what I want to do is I want to create a stored procedure which takes two parameters, maybe gender and the department ID.', 'start': 344.36, 'duration': 8.483}, {'end': 360.346, 'text': 'Okay, for example, if I pass gender as male and department ID as one to your stored procedure,', 'start': 353.143, 'duration': 7.203}, {'end': 366.268, 'text': 'it should give me employees only within that gender and within that department.', 'start': 360.346, 'duration': 5.922}, {'end': 369.789, 'text': 'Okay, so your stored procedure needs to have these parameters.', 'start': 366.768, 'duration': 3.021}, {'end': 371.53, 'text': "Okay, so let's see how to do that.", 'start': 370.089, 'duration': 1.441}, {'end': 376.769, 'text': 'So as usual, to create a stored procedure, we use create procedure command.', 'start': 373.208, 'duration': 3.561}], 'summary': 'Different ways to execute stored procedures and create one with parameters.', 'duration': 59.116, 'max_score': 280.303, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g280303.jpg'}, {'end': 440.065, 'src': 'heatmap', 'start': 406.256, 'weight': 0.787, 'content': [{'end': 411.962, 'text': 'So for them to be able to pass the values for gender and department ID, they should be parameters.', 'start': 406.256, 'duration': 5.706}, {'end': 416.547, 'text': 'Just like how functions have parameters in C sharp or any other programming language.', 'start': 412.002, 'duration': 4.545}, {'end': 419.749, 'text': 'stored procedures can also have parameters.', 'start': 416.867, 'duration': 2.882}, {'end': 425.474, 'text': "okay, so one is the gender parameter, and if you look at gender, it's text here.", 'start': 419.749, 'duration': 5.725}, {'end': 432.599, 'text': 'so the data type is going to be n where care of maybe 20, and department ID.', 'start': 425.474, 'duration': 7.125}, {'end': 434.481, 'text': 'department ID is going to be integer.', 'start': 432.599, 'duration': 1.882}, {'end': 440.065, 'text': 'so department ID, integer as begin.', 'start': 434.481, 'duration': 5.584}], 'summary': 'Stored procedures can have parameters like gender (nvarchar(20)) and department id (integer).', 'duration': 33.809, 'max_score': 406.256, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g406256.jpg'}, {'end': 526.818, 'src': 'embed', 'start': 496.815, 'weight': 3, 'content': [{'end': 500.798, 'text': 'okay, so these parameters are like placeholders.', 'start': 496.815, 'duration': 3.983}, {'end': 509.683, 'text': "when users execute your stored procedure, they're going to pass in values for this gender and department ID, which will be replaced at execution time.", 'start': 500.798, 'duration': 8.885}, {'end': 512.865, 'text': "okay, so let's create the stored procedure.", 'start': 509.683, 'duration': 3.182}, {'end': 517.227, 'text': 'so to create that, select the entire stored procedure, click execute button.', 'start': 512.865, 'duration': 4.362}, {'end': 518.568, 'text': 'command completed successfully.', 'start': 517.227, 'duration': 1.341}, {'end': 526.818, 'text': 'Now, if you refresh the stored procedures folder, you should see SPGET employees by gender and department.', 'start': 518.976, 'duration': 7.842}], 'summary': 'Creating a stored procedure with parameters for gender and department id, resulting in success.', 'duration': 30.003, 'max_score': 496.815, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g496815.jpg'}, {'end': 566.945, 'src': 'embed', 'start': 539.141, 'weight': 5, 'content': [{'end': 544.482, 'text': "If I don't pass the parameters, and if I try to execute that stored procedure, see, highlight that.", 'start': 539.141, 'duration': 5.341}, {'end': 546.754, 'text': 'and then press Execute.', 'start': 545.373, 'duration': 1.381}, {'end': 548.455, 'text': "What's gonna happen?", 'start': 547.294, 'duration': 1.161}, {'end': 557.3, 'text': 'this procedure, or function spGetEmployeesByGenderInDepartment, expects parameter at gender which was not supplied, and that makes sense.', 'start': 548.455, 'duration': 8.845}, {'end': 560.482, 'text': "it's expecting a gender parameter which is not supplied.", 'start': 557.3, 'duration': 3.182}, {'end': 563.183, 'text': 'so we need to pass in the gender parameter.', 'start': 560.482, 'duration': 2.701}, {'end': 566.945, 'text': 'since gender is of type nvacar, I have to use single quotes.', 'start': 563.183, 'duration': 3.762}], 'summary': 'Stored procedure spgetemployeesbygenderindepartment expects gender parameter not supplied.', 'duration': 27.804, 'max_score': 539.141, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g539141.jpg'}], 'start': 223.258, 'title': 'Stored procedures benefits and execution', 'summary': 'Discusses benefits of using stored procedures, including security and reducing network traffic, and details various ways to execute them. it also explains creating stored procedures with parameters, highlighting the use of gender and department id parameters.', 'chapters': [{'end': 496.815, 'start': 223.258, 'title': 'Stored procedures benefits and execution', 'summary': 'Discusses the benefits of using stored procedures, such as security and reducing network traffic, and details various ways to execute stored procedures, including using different keywords and graphical execution. it also explains the creation of a stored procedure with parameters and the syntax for defining parameters and filtering data.', 'duration': 273.557, 'highlights': ['Stored procedures offer benefits such as security and reducing network traffic Stored procedures provide security and help in reducing network traffic, offering advantages beyond just convenience.', 'Different methods for executing stored procedures are explained, including using keywords and graphical execution The chapter details various ways to execute stored procedures, such as using execute keyword, full execute keyword, and graphical execution by right-clicking on the stored procedure.', 'Creation of a stored procedure with parameters is explained, including defining parameters and filtering data based on user input The process of creating a stored procedure with parameters is explained, including defining parameters like gender and department ID, and filtering data based on user input.']}, {'end': 637.792, 'start': 496.815, 'title': 'Creating stored procedure with parameters', 'summary': 'Demonstrates the process of creating a stored procedure with parameters, highlighting the use of gender and department id parameters and the execution of the stored procedure with specific parameter values.', 'duration': 140.977, 'highlights': ['When executing the stored procedure, users pass values for gender and department ID, which are replaced at execution time.', 'The created stored procedure expects gender and department ID parameters, with an example demonstrated using male employees within department ID 1.', 'Demonstrates the consequences of not passing parameters when executing the stored procedure, resulting in an error message about the missing gender parameter.']}], 'duration': 414.534, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g223258.jpg', 'highlights': ['Stored procedures offer benefits such as security and reducing network traffic', 'Different methods for executing stored procedures are explained', 'Creation of a stored procedure with parameters is explained', 'When executing the stored procedure, users pass values for gender and department ID', 'The created stored procedure expects gender and department ID parameters', 'Demonstrates the consequences of not passing parameters when executing the stored procedure']}, {'end': 1008.887, 'segs': [{'end': 748.409, 'src': 'embed', 'start': 663.352, 'weight': 0, 'content': [{'end': 670.831, 'text': 'so it is trying to convert this mail you know string of type and where char into integer and we get that error.', 'start': 663.352, 'duration': 7.479}, {'end': 679.015, 'text': 'Okay. So when you have multiple parameters that a stored procedure is expecting and if you are passing just values,', 'start': 671.271, 'duration': 7.744}, {'end': 685.338, 'text': 'the values order you know the order in which you pass them is important.', 'start': 679.015, 'duration': 6.323}, {'end': 688.139, 'text': 'Okay, The first parameter will be used.', 'start': 685.778, 'duration': 2.361}, {'end': 689.8, 'text': 'I mean the value here.', 'start': 688.139, 'duration': 1.661}, {'end': 696.643, 'text': 'The first argument will be used with the first parameter and the second argument will be used with the second parameter.', 'start': 690.54, 'duration': 6.103}, {'end': 698.984, 'text': "Okay, that's why the order is important.", 'start': 697.443, 'duration': 1.541}, {'end': 712.03, 'text': "But if you use the parameter names like this, let's say I want to pass 1 to at department ID, I can specify the name of the parameter like so.", 'start': 699.504, 'duration': 12.526}, {'end': 716.652, 'text': 'And similarly, I can specify the name of the parameter for gender.', 'start': 712.61, 'duration': 4.042}, {'end': 722.892, 'text': 'So when I execute this now, I will have no issues because you are specifying the name of the parameter.', 'start': 717.95, 'duration': 4.942}, {'end': 733.496, 'text': 'So SQL Server knows this one is meant to be the value for department ID parameter and male is the value for gender parameter.', 'start': 723.752, 'duration': 9.744}, {'end': 741.479, 'text': "It's only when you don't specify the names of the parameter, the order in which you pass the parameters is important.", 'start': 733.876, 'duration': 7.603}, {'end': 744.201, 'text': 'All right.', 'start': 743.84, 'duration': 0.361}, {'end': 748.409, 'text': 'And Okay.', 'start': 747.182, 'duration': 1.227}], 'summary': 'Specify parameter names to avoid ordering issues in stored procedures.', 'duration': 85.057, 'max_score': 663.352, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g663352.jpg'}, {'end': 842.3, 'src': 'heatmap', 'start': 757.978, 'weight': 2, 'content': [{'end': 764.582, 'text': "let's say I have created two procedures until now as we get employees and as we get employees by gender and department.", 'start': 757.978, 'duration': 6.604}, {'end': 770.386, 'text': 'now, if I want to view the text of these stored procedures, what are the different ways that are available?', 'start': 764.582, 'duration': 5.804}, {'end': 773.728, 'text': 'one way is to simply right click on that stored procedure.', 'start': 770.386, 'duration': 3.342}, {'end': 778.411, 'text': 'script stored procedure as create two new query editor window.', 'start': 773.728, 'duration': 4.683}, {'end': 783.053, 'text': 'This generates the contents of that stored procedure.', 'start': 779.131, 'duration': 3.922}, {'end': 783.533, 'text': 'Look at this.', 'start': 783.073, 'duration': 0.46}, {'end': 786.415, 'text': 'This is the stored procedure definition that we have created.', 'start': 783.874, 'duration': 2.541}, {'end': 791.818, 'text': 'Create procedure, procedure name, as, begin, end, and then our query.', 'start': 786.775, 'duration': 5.043}, {'end': 796.721, 'text': 'This is one way to look at the definition of a stored procedure.', 'start': 792.178, 'duration': 4.543}, {'end': 801.062, 'text': 'And the other way is to use a system stored procedure.', 'start': 797.577, 'duration': 3.485}, {'end': 807.01, 'text': 'You know, these stored procedures that we have created here are user-defined stored procedures.', 'start': 801.122, 'duration': 5.888}, {'end': 808.552, 'text': 'These are not system stored procedures.', 'start': 807.15, 'duration': 1.402}, {'end': 814.965, 'text': 'Now, SQL Server, you know, has some system stored procedures defined.', 'start': 808.872, 'duration': 6.093}, {'end': 817.89, 'text': 'And we use it for certain tasks.', 'start': 815.806, 'duration': 2.084}, {'end': 821.295, 'text': 'For example, I want to find the text of a stored procedure.', 'start': 817.97, 'duration': 3.325}, {'end': 827.084, 'text': 'How do I do that? I can use a system stored procedure called sp underscore help text.', 'start': 821.615, 'duration': 5.469}, {'end': 829.487, 'text': 'So look at this.', 'start': 828.446, 'duration': 1.041}, {'end': 831.809, 'text': 'This is the name of the system stored procedure.', 'start': 829.647, 'duration': 2.162}, {'end': 835.153, 'text': 'SP underscore help text.', 'start': 832.37, 'duration': 2.783}, {'end': 842.3, 'text': 'SP help text and then if I pass in the name of the stored procedure there, SP get employees.', 'start': 836.454, 'duration': 5.846}], 'summary': 'Two ways to view stored procedure text: right-click or use system stored procedure sp_help_text.', 'duration': 55.525, 'max_score': 757.978, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g757978.jpg'}, {'end': 869.288, 'src': 'heatmap', 'start': 844.454, 'weight': 0.937, 'content': [{'end': 850.699, 'text': 'and then when I select them together and execute this, look at this, I get the text of my stored procedure.', 'start': 844.454, 'duration': 6.245}, {'end': 857.264, 'text': 'You can then copy that, paste it here and see how does the implementation of the stored procedure looks like.', 'start': 850.979, 'duration': 6.285}, {'end': 869.288, 'text': 'So, to view the definition of a stored procedure, you can either right click on that script stored procedure as create to new query editor window,', 'start': 858.124, 'duration': 11.164}], 'summary': 'Selecting and executing stored procedure reveals its text for viewing.', 'duration': 24.834, 'max_score': 844.454, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g844454.jpg'}, {'end': 971.435, 'src': 'embed', 'start': 940.956, 'weight': 1, 'content': [{'end': 944.08, 'text': "It's going to conflict with the user stored procedure.", 'start': 940.956, 'duration': 3.124}, {'end': 953.511, 'text': "So to avoid problems like this, it's always better not to prefix user defined stored procedures with SP underscore prefix.", 'start': 944.841, 'duration': 8.67}, {'end': 966.251, 'text': 'All right, so to change the stored procedure, now once we have created a stored procedure, for example, I have the stored procedure SP get employees.', 'start': 958.365, 'duration': 7.886}, {'end': 971.435, 'text': "After I have created the stored procedure, let's say I want to change its implementation in some way.", 'start': 966.732, 'duration': 4.703}], 'summary': 'Avoid prefixing user stored procedures with sp_ to prevent conflicts.', 'duration': 30.479, 'max_score': 940.956, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g940956.jpg'}], 'start': 638.995, 'title': 'Stored procedure management', 'summary': 'Explains the importance of parameter order and names in passing values to stored procedures, with examples of converting data types and specifying parameter names to avoid errors. it also covers how to view the definition of a stored procedure using system stored procedures, including sp_help_text, best practices for naming user-defined stored procedures, as well as the challenges and solutions when modifying a stored procedure.', 'chapters': [{'end': 748.409, 'start': 638.995, 'title': 'Stored procedure parameter passing', 'summary': 'Explains the importance of parameter order and names in passing values to stored procedures, with examples of converting data types and specifying parameter names to avoid errors.', 'duration': 109.414, 'highlights': ['When passing values to stored procedures, the order of parameters is important, with the first argument used for the first parameter and the second argument for the second parameter.', 'Specifying parameter names when passing values to stored procedures can prevent errors related to parameter order, as SQL Server will recognize the intended parameter for each value.', "Converting data types when passing values to stored procedures can lead to exceptions, such as 'error converting data type var char to integer'."]}, {'end': 1008.887, 'start': 748.409, 'title': 'Viewing and modifying stored procedures', 'summary': 'Covers how to view the definition of a stored procedure using system stored procedures, including sp_help_text, and the best practices for naming user-defined stored procedures, as well as the challenges and solutions when modifying a stored procedure.', 'duration': 260.478, 'highlights': ['The chapter covers how to view the definition of a stored procedure using system stored procedures, including sp_help_text. It demonstrates the use of the system stored procedure sp_help_text to retrieve the text of a stored procedure, providing an alternative method to view the definition.', 'The best practices for naming user-defined stored procedures are discussed, advising against using the SP_ prefix to avoid ambiguity and potential conflicts with system stored procedures. It highlights the recommendation to avoid using the SP_ prefix for user-defined stored procedures to prevent ambiguity and potential conflicts with system stored procedures in future releases.', 'The challenges and solutions when modifying a stored procedure are explained, including encountering errors when attempting to modify an existing stored procedure. It explains the scenario where errors occur when attempting to modify an existing stored procedure, emphasizing the challenge of re-creating a stored procedure with the same name and providing a solution for the issue.']}], 'duration': 369.892, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g638995.jpg', 'highlights': ['Specifying parameter names when passing values to stored procedures can prevent errors related to parameter order, as SQL Server will recognize the intended parameter for each value.', 'The best practices for naming user-defined stored procedures are discussed, advising against using the SP_ prefix to avoid ambiguity and potential conflicts with system stored procedures. It highlights the recommendation to avoid using the SP_ prefix for user-defined stored procedures to prevent ambiguity and potential conflicts with system stored procedures in future releases.', 'The chapter covers how to view the definition of a stored procedure using system stored procedures, including sp_help_text. It demonstrates the use of the system stored procedure sp_help_text to retrieve the text of a stored procedure, providing an alternative method to view the definition.', "Converting data types when passing values to stored procedures can lead to exceptions, such as 'error converting data type var char to integer'.", 'The challenges and solutions when modifying a stored procedure are explained, including encountering errors when attempting to modify an existing stored procedure. It explains the scenario where errors occur when attempting to modify an existing stored procedure, emphasizing the challenge of re-creating a stored procedure with the same name and providing a solution for the issue.', 'When passing values to stored procedures, the order of parameters is important, with the first argument used for the first parameter and the second argument for the second parameter.']}, {'end': 1209.464, 'segs': [{'end': 1064.449, 'src': 'embed', 'start': 1009.187, 'weight': 0, 'content': [{'end': 1014.73, 'text': 'Our intention here is to change the definition of that stored procedure not to create another stored procedure.', 'start': 1009.187, 'duration': 5.543}, {'end': 1023.315, 'text': 'So if you want to change the definition of the stored procedure then you say alter procedure and I press F5 the stored procedure gets changed.', 'start': 1015.051, 'duration': 8.264}, {'end': 1026.676, 'text': 'Now if we execute that we should have the name sorted.', 'start': 1023.695, 'duration': 2.981}, {'end': 1033.442, 'text': 'So we use alter procedure statement to change the definition of stored procedure.', 'start': 1028.397, 'duration': 5.045}, {'end': 1038.827, 'text': 'And to delete the stored procedure, we use drop procedure procedure name.', 'start': 1033.823, 'duration': 5.004}, {'end': 1043.609, 'text': 'Just like if you want to drop a table, you will use drop table table name.', 'start': 1039.166, 'duration': 4.443}, {'end': 1052.037, 'text': 'So similarly, to drop a procedure, you will say drop procedure and procedure name.', 'start': 1044.55, 'duration': 7.487}, {'end': 1055.54, 'text': 'For example, I want to drop or delete SP get employees.', 'start': 1052.057, 'duration': 3.483}, {'end': 1057.301, 'text': 'I just pass it there.', 'start': 1056.24, 'duration': 1.061}, {'end': 1064.449, 'text': "I press F5 and then if I refresh the stored procedures folder, it's gone now.", 'start': 1057.745, 'duration': 6.704}], 'summary': "Use 'alter procedure' to change stored procedure definition and 'drop procedure' to delete it.", 'duration': 55.262, 'max_score': 1009.187, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g1009187.jpg'}, {'end': 1117.631, 'src': 'embed', 'start': 1090.055, 'weight': 2, 'content': [{'end': 1100.09, 'text': 'this is not encrypted at the moment, so when I use SP underscore help text and when I press F5, I am able to get the text of that stored procedure.', 'start': 1090.055, 'duration': 10.035}, {'end': 1103.92, 'text': "So that's how the stored procedure is implemented.", 'start': 1101.738, 'duration': 2.182}, {'end': 1110.825, 'text': 'Now, if I want to encrypt the contents, the text of the stored procedure, I can do that.', 'start': 1103.94, 'duration': 6.885}, {'end': 1117.631, 'text': 'How do I do that? All you have to do is to use the switch, this option, with encryption.', 'start': 1111.126, 'duration': 6.505}], 'summary': "Stored procedures can be encrypted using the 'with encryption' switch, providing security for the stored procedure text.", 'duration': 27.576, 'max_score': 1090.055, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g1090055.jpg'}, {'end': 1193.186, 'src': 'embed', 'start': 1167.646, 'weight': 3, 'content': [{'end': 1173.048, 'text': 'so once a stored procedure is encrypted, you cannot view the text of that stored procedure.', 'start': 1167.646, 'duration': 5.402}, {'end': 1177.63, 'text': 'but however, if you want to delete the stored procedure, you can go ahead and delete it.', 'start': 1173.048, 'duration': 4.582}, {'end': 1185.053, 'text': 'you know, just right click and select delete it gets deleted, but you cannot view the contents of a stored procedure that is encrypted.', 'start': 1177.63, 'duration': 7.423}, {'end': 1193.186, 'text': 'All right, in the next session we will see how to create and invoke a stored procedure with output parameters.', 'start': 1187.54, 'duration': 5.646}], 'summary': 'Encrypted stored procedures cannot be viewed but can be deleted. next session will cover creating and invoking stored procedures with output parameters.', 'duration': 25.54, 'max_score': 1167.646, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g1167646.jpg'}], 'start': 1009.187, 'title': 'Managing stored procedures', 'summary': 'Covers altering and dropping stored procedures, encrypting their text, and the implications of encryption. it provides examples and highlights the inability to view encrypted text. additionally, it mentions upcoming sessions on stored procedures with output parameters and resources for asp.net and c sharp interview questions.', 'chapters': [{'end': 1064.449, 'start': 1009.187, 'title': 'Alter and drop stored procedures', 'summary': "Discusses using the 'alter procedure' statement to change the definition of a stored procedure and the 'drop procedure' statement to delete a stored procedure, providing examples and the process for executing both actions.", 'duration': 55.262, 'highlights': ["Using 'alter procedure' statement to change the definition of a stored procedure by executing 'alter procedure' followed by the procedure name and the new definition, resulting in the modification of the stored procedure.", "Using 'drop procedure' statement to delete a stored procedure by executing 'drop procedure' followed by the procedure name, leading to the removal of the specified stored procedure.", "Provided examples of using 'alter procedure' and 'drop procedure' statements, such as altering the definition of a stored procedure and deleting a stored procedure named 'SP get employees' by executing the respective statements and observing the changes."]}, {'end': 1144.556, 'start': 1064.529, 'title': 'Encrypting stored procedures', 'summary': "Explains how to encrypt the text of a stored procedure, illustrating the process and the resulting encryption, safeguarding the stored procedure's content.", 'duration': 80.027, 'highlights': ["The process of encrypting the text of a stored procedure is demonstrated, ensuring security of the stored procedure's content.", 'The steps involved in encrypting the stored procedure are clearly presented, making it simple to implement.', 'The successful completion of the encryption process is shown with the appearance of a lock symbol, providing a visual indication of the encrypted status.']}, {'end': 1209.464, 'start': 1144.556, 'title': 'Stored procedure encryption', 'summary': 'Discusses the inability to view encrypted text of a stored procedure, highlighting the error message received and the inability to retrieve the text, with a mention of upcoming sessions on stored procedures with output parameters and resources for asp.net and c sharp interview questions.', 'duration': 64.908, 'highlights': ['Once a stored procedure is encrypted, you cannot view the text of that stored procedure, but can delete it.', 'Mention of upcoming sessions on creating stored procedures with output parameters.', 'Resources for ASP.NET and C Sharp interview questions mentioned.']}], 'duration': 200.277, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/Qu3E-oncF3g/pics/Qu3E-oncF3g1009187.jpg', 'highlights': ["Using 'alter procedure' statement to change the definition of a stored procedure by executing 'alter procedure' followed by the procedure name and the new definition, resulting in the modification of the stored procedure.", "Using 'drop procedure' statement to delete a stored procedure by executing 'drop procedure' followed by the procedure name, leading to the removal of the specified stored procedure.", "The process of encrypting the text of a stored procedure is demonstrated, ensuring security of the stored procedure's content.", 'Once a stored procedure is encrypted, you cannot view the text of that stored procedure, but can delete it.']}], 'highlights': ['Stored procedures save and reuse queries, enhancing efficiency and reducing redundancy.', 'Stored procedures simplify query execution, saving time and effort.', 'Demonstrates practical implementation and usage of stored procedures.', "Stored procedure is created using 'create procedure' command, followed by a specified name.", "Naming convention for stored procedures involves prefixing the name with 'sp' for easy identification.", 'Emphasizes the potential complexity of procedures, highlighting scalability and versatility.', 'Stored procedures offer benefits such as security and reducing network traffic.', 'Different methods for executing stored procedures are explained.', 'Creation of a stored procedure with parameters is explained.', 'Demonstrates the consequences of not passing parameters when executing the stored procedure.', 'Specifying parameter names when passing values to stored procedures can prevent errors.', 'Best practices for naming user-defined stored procedures are discussed.', 'Covers how to view the definition of a stored procedure using system stored procedures.', 'Converting data types when passing values to stored procedures can lead to exceptions.', 'Challenges and solutions when modifying a stored procedure are explained.', 'The order of parameters is important when passing values to stored procedures.', "Using 'alter procedure' statement to change the definition of a stored procedure.", "Using 'drop procedure' statement to delete a stored procedure.", 'The process of encrypting the text of a stored procedure is demonstrated.']}