11. Stored Procedure in Microsoft SQL Server. HOW to implement it and WHY it is important?

in SQL Server a stored procedure is a group of one or more transaxical statements compiled into a single execution plan and it can be executed multiple times by using stored procedures in your database systems you can achieve consistent implementation of logic across your applications as it offers a single point of control for ensuring that business rules are correctly enforced for this lesson you'll learn how to create a user-defined stored procedure and explore its numerous benefits like reduce server and client Network traffic is stronger security code reusability maintainability and overall performance of your database systems hi this is Joy Ed go and welcome to lesson 11 on designing database Solutions with Microsoft SQL Server 2022 [Music] stored procedures resemble construct in other programming languages because they can contain programming statements that perform operations in the database it can accept input parameters it can return status value to indicate a success or a failure or it can return multiple values to the calling program using output parameters to create a stored procedure simply type create procedure followed by a name say USB get employees USB stands for user-defined stored procedure and for this demonstration I will be using the same database in the previous lessons on SQL views and functions now parameters are optional in stored procs for this first example we'll just get all the employees from our database similar to what we did when we create views in lesson 9. say I'll join the employees in the Departments tables and then I'll retrieve only the employee ID and the formatted name this is the function that we created in lesson 10 the contact number and the department and then I'll execute this code to create our stored procedure you can check your newly created stored proc under the programmability folder stored procedures and now to execute the stored Pro you simply type exact followed by the procedure name and then hit execute and then that's it now just to give you a quick comparison with SQL view we can produce a similar result set by creating a view like this of course views are not pre-compiled they are just stored queries and are only treated as simply a virtual table thus we need to issue a select statement to query through it obviously we produce the same result set but one major advantage of a stored procedure is flexibility as it can accept parameters which views don't have so I'll modify this stored proc to include one parameter amp ID of type int and then add a where Clause here to filter only the employee based on the ID number I'll execute this and now to test this procedure I'll pass in the required employee ID say 2001 and we were able to retrieve a specific employee 2001 which is Clark Kent let's try another one 2005 and we have Tony Stark now if you're wondering what would happen if you don't pass any input value here like this of course it will trigger an error saying that parameter was not supplied now if you're tempted to create two different stored procedures just to get the employees records one that doesn't accept any parameters and one that accepts the employee ID to the three specific record then consider this example I will not be creating two separate procedures but I'll just modify this one and set the default value for this parameter to null now I can apply this simple t-sql codes to handle that issue I'll check if the amp ID is null meaning the calling program did not pass any parameter then I'll simply select through this view employees as in get all the employees however if the employee ID parameter was applied then I'll select through this view employees where the employee ID is equal to the input parameter value now I'll execute this and then to test it I'll execute USB get employees without a parameter and it works now if we Supply an employee ID we should be able to get only the specific employee record ing flexibility stored procedures are not only used to retrieve records but they are also ideal for insert update and delete operations it offers stronger security since multiple users and client programs can perform any operations on the underlying database objects even if the users and programs do not have direct permissions on those underlying objects when calling a procedure over the network only the call to execute the procedure is visible therefore malicious users cannot see tables and database object names they cannot embed transact SQL statements on their own nor search for critical information using procedure parameters helps guard against SQL injection attacks since parameter input is treated as a literal value and not as executable code it is more difficult for an attacker to insert a command into the transact SQL statements inside the procedure to compromise security to create a stored procedure that inserts new employee record I'll give it a name of USB ad employee and then I'll provide the following parameters last name first name middle name birthday email address hourly rate and Department and then simply issue the insert command like this insert employees and provide the values from the supplied parameters note that we don't have to supply the employee ID since this field was configured to be Auto incremented I'll execute this code and then I'll test it by supplying values to this new employee record I'll execute this now notice that this stored proc didn't give us any result so we need to select all the employees to verify if we've successfully inserted this new employee and we did we added employee 2008. now imagine you are creating a client application wherein the user fill in this new employee information but how will your app know if the record was inserted successfully or what was the employee ID number generated by the server we can modify this stored proc to return meaningful information to the coding program like we could return the last identity value that is generated by the statement in this case the auto-generated employee ID in the case that there is no successful insert this at identity system function call would return null I'll execute this and then try to insert new employee to our database and great we have a successful return value now depending on your need you might want to return a row set to the calling program I'll modify this again and instead of just the ID number I'll return all the information of this newly inserted employee I'll execute this code and test it one more time and we were able to successfully insert a new employee record and return it as well to the coding program creating stored procedures to update and delete are just the same for example if you have a client application or in the user can update the employee's hourly rate like an HR Personnel then you can do something like this this procedure accepts two input parameters the amp ID and the newly and then we update the employees table and set the hourly rate to this new rate and it should be this employee ID only now you might want to return the number of rows affected by the update command obviously we only expect here either one or zero so I'll execute this and then test this by updating the rate of employee ID 2010 to a new rate of 5000. then hit execute so one means we found that record and the update was successful we can view all the records to verify that and then let's try it again and update the date of employee that doesn't exist and updated row is zero now what I have shown you here is just the basics of stored procs to get you started and there are numerous advantages to using this in your database systems aside from stronger security code reusability and flexibility is stored procs can significantly reduce Network traffic because only the call to execute the procedure is sent across the network it also offers easier maintenance since only the procedures must be updated for any changes in the underlying database it keeps the database operation in the data tier and the application tier remains separate and lastly stored procs improve the database system performance because by default a procedure compiles the first time it is executed and then creates an execution plan that is reused for subsequent executions since the query processor does not have to create a new plan it typically takes less time to process the procedure and again thank you very much for watching I hope you've learned something of value here because up next we'll discuss triggers and how to implement all the trails to help this Channel please click the like And subscribe button for more video lectures just like this this is Joe edgo and hope to see you again in our next lesson [Music] thank you

Channel: Education is Life

Views: 800

Keywords: Stored Proc, procedure, @@IDENTITY, @@ROWCOUNT, IF, ELSE, Null parameter, parameter, INSERT, UPDATE, SELECT, Flexibility, security, code reusability, maintenance

Id: Kvrojn6UmE0

Length: 12min 8sec (728 seconds)

Published: Sun Apr 16 2023