Dec 07, 2018 
630.9k
23
NovBlack Friday Deal : Up to 40% OFF! + 2 free self-paced courses + Free Ebook - SCHEDULE CALL
- SQL Server Blogs -
Take an example of one database application where a certain part of the code is repeated number of times. So, will you like to write or run the same code again and again? Obviously, No. But we need a good technique here that can be used to solve the issue and reducing the overall code lines too. Here, the role of Stored Procedures comes into the picture.
Let us discuss what is a stored procedure first then we will learn how to insert multiple rows using stored in SQL. We will also tell you the benefits and drawbacks of stored procedures and why they are used by SQL developers frequently.
A Stored Procedure in SQL could be defined as the group of one or more database statements stored in the database data dictionary and they are either called through a remote program, another stored procedure or you a command line too. Stored Procedures in SQL are generally abbreviated as SPs.
The main parts of a stored procedure are given as below.
Inputs: Stored Procedures can accept values of parameters as inputs. Based on the nature of the parameter and how they are defined, modified values are passed back to the calling program.
Execution: Stored procedures are used to execute SQL statements by utilizing conditional logic like IF, THEN, CASE statement, or other looping structures. A stored procedure can call another stored procedure and it is very handy for manipulating outputs of SQL queries through cursors. With the help of cursors, outputs can be accessed row by row. If cursors are not used effectively then it could reduce the database performance, so you have to be intelligent about the usage of cursors.
Outputs: A stored procedure returns single value like number or text value or a result set too. Based on the nature of input defined, modified values are propagated back to the calling procedure.
Stored procedures can be called within the SQL server and other programming languages like PHP, C# and more. The syntax for creating a stored procedure in SQL is quite simpler. Here is an example of how to create Stored Procedures within SQL.
This was the basic syntax, let us put the simple values in the above syntax to understand the same deeply.
Read: MSBI Interview Questions & Answers for Fresher, Experienced
The biggest benefit of a stored procedure is that data access logic can be centralized to a single place and it is easy to optimize for DBAs as well. There is one security benefit too where you can grant execution rights to the stored procedure but users don’t have to grant read/write permission here. This is just an excellent technique against SQL injection.
Stored procedures had certain downsides where maintenance is associated with basic SQL operations. Let us say for each table you have a minimum of 4 underlying procedures, these are Insert, Update, Delete, and Select statements etc. If we take a database of 400 tables then there would be 1600 procedures in minimum. We assume here that there are no duplicate values stored in database tables.
Learn SQL Server in the Easiest Way
Till the time, we have discussed the basic definition of the stored procedure, its syntax, and why are they considered important within the SQL server. As we know that a stored procedure is used in SQL so that the same code can be used over again and again. So, if there is one SQL query that has to be rewritten, again and again, you should save it as a stored procedure and call it to execute every time. This is possible by passing values to the stored procedures so that it can behave based on the values passed in the procedure. Think for a while, we had the employee table having four parameters – ID, Name, Age, and contact.
| Employee ID | Name | Age | Mobile |
| 001 | Sidheswar | 25 | 9938885469 |
| 002 | Pritish | 32 | 9178524365 |
So, let us see how to retrieve the employee table using a stored procedure within the SQL server.
To run or execute the stored procedure within SQL server, use the following command.
Execute Employee Details
Read: SSRS Sub Reports and deployment process-How to do it
Here ‘employee details’ is a user-defined name, you could use any other name of your choice too. Once you know how to create a stored procedure, retrieve data from the table and executing a stored procedure, this is the right time to start our discussion for multiple row insertion in the Employee table.
With this syntax, you could add multiple rows in the table by separating the values with a comma. In the next step, we will learn how to run the parametrized stored procedure within the SQL server.
Execute employee_insert 003, ‘xyz’, 27, 1234567890
Keep in mind that size of the parameter should always be equal to the size of the column declared earlier. 
A Stored Procedure is a group of SQL statements that have been created and stored in the database. As the stored procedure is accepting input parameters, it can be shared or used over the network as well by several clients using different data inputs. In this way, stored procedures will reduce network traffic and increase the overall performance. In case, changes are made to a particular stored procedure then they are immediately reflected to other connected clients. 
Here are some of the major benefits of Stored Procedures that will help you to know why they are so important in SQL Server.
In SQL Server, Stored procedures are majorly divided into three categories. These are – System defined procedures, User-defined procedures, and Extended procedures. 
Let us discuss each of them in brief one by one.
SQL Server Training & Certification
Read: SQL REPLACE() Function: A Step-By-Step Guide
Every technology has certain benefits and drawbacks associated with it. 
The same is the case with stored procedures too. Some of the disadvantages that are worth mentioning include –
SQL Server Training & Certification
Conclusion
I would say that using stored procedures within SQL server have their own place. Developers are using stored procedures frequently for multiple SQL operations. They are actually considered good for inserting values in a table and modifying data. The extensively written stored procedures can pass security tests quickly and are less prone to raise vulnerable testing issues. I hope all your questions related to stored procedures are answered in this blog. If you wish to learn further about SQL, we suggest you join our Online training by clicking here.
FaceBook
Twitter
LinkedIn
Pinterest
Email
A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.
Cyber Security
QA
Salesforce
Business Analyst
MS SQL Server
Data Science
DevOps
Hadoop
Python
Artificial Intelligence
Machine Learning
Tableau
Search Posts
Related Posts
Receive Latest Materials and Offers on SQL Server Course
Interviews
Nov 08, 2024
858.8k
858.8k