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 table using stored procedure in Sql Server with example. And also show you how you can use procedure in SQL Server with example.
![]() |
Sql Server Stored Procedure |
When You working with any data driven application you need database and also need some data manipulation operations in DBMS(Database Management System) such as selection, insertion, updation, deletion and etc.
Your database may contains 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) for secure your data from unauthorized activity.
If you working with sql server then stored procedure is one of the best option for data manipulation operations, because stored procedure accept parameter as argument, and parameterized statements prevent code injection technique such as "SQL Injection" that might destroy your database.
So, Hear i will show you how you can use stored procedure in your sql server database.
Requirement
1) Insert data in table using 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 our actual implementation of our example we need database for demonstration, So first we will create database.
Create Database
CREATE DATABASE db_Codingvila
Before create stored procedure i will show you the syntax of 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 Above procedure then you can see @EmpId, @EmpName and @EmpDesignation where @ can described as a parameter.
Now, i will show you how to create second stored procedure and how you can insert data in table using first created stored procedure, and for that i used temporary table.
Create Second Stored Procedure
Now it's time to call/Execute created stored procedure and insert data in table and also display 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 Above procedure then you can see our First created stored procedure is execute with "EXEC" You also can use "EXECUTE" also, where @Id, @Name and @Designation sending parameters to 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 actual result as per our requirement.
After successful execution of this procedure you can see result as like shown in output window.
Output
![]() |
Sql Server Stored Procedure Example |
Summary
This article explains how to create stored procedure in sql server, how you can do data manipulation operations ad also how you can protect your database from authorized activity and prevent data driven attack like SQL Injection. I hope this article helps you, if you have any query then you can leave your comments in comment box thank you.