Today's Offer - SQL Server Certification Training - Enroll at Flat 10% Off.

- SQL Server Blogs -

How to Insert Multiple Rows Using Stored Procedure in SQL?

In a database application a certain part of the code is repeated number of times, so will you like to write or run the same code over again and again? Obviously not. But we need a good technique here that can be used to solve the issue and reduce the overall lines of coding too. Here, the role of Stored Procedures comes into the picture. Let us discuss what a stored procedure is first then we will learn how to insert multiple rows using stored procedure in SQL. We will also tell you the benefits and drawbacks of stored procedures and why are they used so frequently by SQL developers.

What is a Stored Procedure in SQL Server?

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.

Stored Procedure in SQL 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.

Read: Top 100 SQL Interview Questions and Answers

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.

SQL Server Curriculum 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. [code lang="sql"]CREATE PROCEDURE <owner>.<procedure name> <param> <datatype> AS <Body> [/code] This was the basic syntax, let us put the simple values in the above syntax to understand the same deeply. [code lang="sql"]CREATE PROCEDURE Users_GetUserInfo @login nvarchar(30) = null AS SELECT * from [Users] WHERE ISNULL (@login, login)=Login [/code] 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.

Inserting multiple rows using stored procedures in SQL server

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. [code lang="sql"]CREATE PROCEDURE Employee Details As Begin SELECT * From Employee End [/code] To run or execute the stored procedure within SQL server, use the following command. Execute Employee Details 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. [code lang="sql"]Create Procedure employee_insert (@employeeID int, @name Varchar(30), Age int, @mobile int) AS Begin Insert into employee Value (@employeeId, @name @age, @mobile) End [/code] 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. SQL Server quiz

Benefits of Stored Procedures

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. Benefits of Stored Procedures

Read: What Is Average Salary Of Database Admin In Philippine?

Here are some of the major benefits of Stored Procedures that will help you to know why they are so important in SQL Server.

  • It allows modular programming where the stored procedure is created once and it can be used multiple times in your program.
  • It allows faster execution where a large amount of SQL code can be made to run repetitively and execution becomes much faster than your expectations. They are optimized and parsed when executed for the first time and a compiled part of the code will stay in the cache memory for future reference. It means we don’t have to optimize or parse again and again but pick the code directly from cache memory only.
  • It reduces network traffic by sending a single statement over the network instead of sharing thousands of code lines together.
  • It offers maximum security to the data where users can be granted permissions to execute a stored procedure even if they are not allowed to access the procedure directly. Stored procedures are always executed with maximum security credentials and you could set the limit who can execute the stored procedure. With Stored Procedures, you can minimize script injection attacks too. All input parameters are defined as literals, not the executable code. This makes it harder for hackers to perform an unintended action.
  • It helps in encapsulating business logic It makes sure that key business logic should remain consistent and it is running well on the server.
  • It makes the application maintenance Instead of making changes to each part of an application, just make changes to the stored procedure and you are done. As soon as you will compile the application, the whole program will get benefits from the change. Again, it will help in increasing the quality of the database too.

Types of Stored Procedures

In SQL Server, Stored procedures are majorly divided into three categories. These are – System defined procedures, User-defined procedures, and Extended procedures. Types of Stored Procedures

Let us discuss each of them in brief one by one.

  1. System-defined Stored Procedures – A stored procedure is generally stored within the master database and they will always start with the prefix sp. These procedures are further needed to perform a variety of tasks to support SQL functions and external application calls in the system table.
  2. User-defined Stored Procedures – These stored procedures are stored within a user database that are typically designed to complete multiple tasks in the user database. Here, ‘sp’ is not used as the prefix. If ‘sp’ prefix is added in the beginning then it will search the master database only.
  3. Extended Stored Procedures – These are the procedures calling functions from DLL files. Developers don’t rely on extended stored procedures. So, this is better avoiding them and focus more on system-defined and user-defined procedures only.

free SQL Server demo

Drawbacks of Stored Procedures

Every technology has certain benefits and drawbacks associated with it. Drawbacks of Stored Procedures

Read: Top 50 Datastage Interview Questions and Answers

The same is the case with stored procedures too. Some of the disadvantages that are worth mentioning include –

  • Portability: A stored procedure is generally written in the vendor-specific language and that makes it tough transferring from one installation like SQL Server to Oracle or more.
  • Testing: Debugging or testing a stored procedure is generally tough and you need to put more efforts and resources for the same. There is a set of debugging tools to ease the process still debugging will remain a challenge in stored procedures.
  • Version Control: It is hard to keep track of changes in case of the stored procedure. At the same time, changes tracking is easy in the native code. Most of the times, the create procedure script is added manually to a version control system that consumes a lot of time and efforts.

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.

SQL Tutorial Overview


    Janbask Training

    JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.


Trending Courses

AWS

  • AWS & Fundamentals of Linux
  • Amazon Simple Storage Service
  • Elastic Compute Cloud
  • Databases Overview & Amazon Route 53

Upcoming Class

4 days 24 Nov 2019

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing

Upcoming Class

5 days 25 Nov 2019

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning

Upcoming Class

5 days 25 Nov 2019

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation

Upcoming Class

6 days 26 Nov 2019

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

14 days 04 Dec 2019

Course for testing

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL

Upcoming Class

34 days 24 Dec 2019

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing

Upcoming Class

13 days 03 Dec 2019

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum

Upcoming Class

5 days 25 Nov 2019

SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design

Upcoming Class

8 days 28 Nov 2019

Comments

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews