How To Create and Execute Parameterized Stored Procedure

watch_later Monday, November 19, 2018

Introduction


In this article am going to explain how to create and execute parameterized stored procedure From another stored procedure, how to prevent SQL Injection attacks, how to insert data in the table using stored procedure in SQL Server with example. And also show you how you can use the procedure in SQL Server for example. 
Sql Server Stored Procedure
SQL Server Stored Procedure

When You working with any data-driven application you need the database and also need some data manipulation operations in DBMS(Database Management System) such as selection, insertion, updating, deletion and etc.

Your database may contain some useful and confidential information so it is necessary to protect it from any unauthorized activity and implement some security credentials with your DBMS(Database Management System) to secure your data from unauthorized activity. 

If you working with an SQL server then the stored procedure is one of the best options for data manipulation operations because stored procedure accepts a parameter as an argument, and parameterized statements prevent code injection techniques such as "SQL Injection" that might destroy your database.

So, Here I will show you how you can use stored procedures in your SQL server database.

Requirement


1) Insert data in the table using a stored procedure.
2) Create Stored Procedure in SQL Server.
3) Call or Execute Stored Procedure in SQL Server.
4) Call or Execute Stored Procedure From Another Stored Procedure in SQL Server.
5) Select and Display Inserted Data in Tabular Format in SQL Server Using Stored Procedure.

Implementation


Before, start the actual implementation of our example we need a database for demonstration, So first we will create a database.

Create Database

CREATE DATABASE db_Codingvila
Before creating a stored procedure I will show you the syntax of the stored procedure in SQL Server.

Syntax

CREATE PROCEDURE Your_Procedure_Name
-- list of parameters i.g: @Id INT = 0, @EmpName VARCHAR(50)=''
AS
BEGIN
-- Sql statements
END
Now, we will start to write our stored procedure something like :

Create First Stored Procedure

--****************************************
-- CREATED ON 20/11/2018 BY NIKUNJ SATAIYA
--****************************************

-- Create First Stored Procedure

CREATE PROCEDURE Employee_Insert
@EmpId INT, 
@EmpName VARCHAR(50),
@EmpDesignation  VARCHAR(50)
AS
BEGIN
-- Declare temporary table

DECLARE @Temp1 TABLE (EmployeeId INT, EmployeeName VARCHAR(50), EmployeeDesignation VARCHAR(50))

-- insert records in temporary table

INSERT INTO @temp1 (EmployeeId, EmployeeName, EmployeeDesignation)
VALUES(1,'Nikunj Satasiya','Software Engineer'),
(2,'Hiren Dobariya','Web Developer'),
(3,'Vivek Ghadiya','Business Development Executive'),
(4,'Pratik Pansuriya','Business Development Executive'),
(5,'Milan Lathiya','Software Engineer')

-- Select all records from temporary table
SELECT EmployeeId, EmployeeName, EmployeeDesignation FROM @Temp1
-- Select specific records from temporary table
SELECT EmployeeId, EmployeeName, EmployeeDesignation FROM @Temp1 WHERE EmployeeId = @EmpId AND EmployeeName = @EmpName AND EmployeeDesignation = @EmpDesignation

END
If You analyzed the Above procedure then you can see @EmpId, @EmpName, and @EmpDesignation where @ can describe as a parameter.

Now, I will show you how to create a second stored procedure and how you can insert data in the table using the first created stored procedure, and for that, I used a temporary table.

Create Second Stored Procedure


Now it's time to call/Execute created the stored procedure and insert data in the table and also display the result in tabular format, for that here we will create another stored procedure and will call/Execute created stored procedure. 
--****************************************
-- CREATED ON 20/11/2018 BY NIKUNJ SATAIYA
--****************************************

-- Create Second Stored Procedure

CREATE PROCEDURE Employee_GetData
@Id INT, 
@Name VARCHAR(50),
@Designation  VARCHAR(50)
AS
BEGIN
-- Execute First Procedure
EXECUTE Employee_Insert @EmpId = @Id , @EmpName = @Name, @EmpDesignation = @Designation

END
Again If You analyzed the Above procedure then you can see our First created stored procedure is executed with "EXEC" You also can use "EXECUTE" also, where @Id, @Name, and @Designation sending parameters to the first stored procedure.
--Execute Second Procedure By Passing Paramters
EXEC Employee_GetData @id=1, @name='Nikunj Satasiya', @Designation ='Software Engineer'
Now we will call/Execute our second created procedure that will call/Execute our first created procedure with the actual result as per our requirement.

After the successful execution of this procedure, you can see results as shown in the output window.

Output

Sql Server Stored Procedure Example
SQL Server Stored Procedure Example


Summary


This article explains how to create a stored procedure in an SQL server, how you can do data manipulation operations ad also how you can protect your database from the authorized activity and prevent a data-driven attacks like SQL Injection. I hope this article helps you, if you have any query then you can leave your comments in the comment box thank you.

Codingzee provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

If you have any questions regarding this article/blog you can contact us on codingzee@gmail.com

sentiment_satisfied Emoticon