In this article I am going to explain how to create and execute parameterized stored procedure from another stored procedure, how to prevent SQL Injection attacks, and how to insert data in the table using stored procedure in SQL Server with an example. And also I'll show you how you can use the procedure in SQL Server with an example.
 
When working with any data-driven application you need the database and you 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're working with an SQL server then a stored procedure is one of the best options for data manipulation operations because a stored procedure accepts parameters 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.
 
Requirements
Insert data in the table using a stored procedure.
Create a Stored Procedure in SQL Server.
Call or Execute Stored Procedure in SQL Server.
Call or Execute Stored Procedure From Another Stored Procedure in SQL Server.
Select and Display Inserted Data in Tabular Format in SQL Server Using Stored Procedure.

Before starting the actual implementation of our example we need a database for demonstration, so first we will create a database.
 
Create Database
    CREATE DATABASE db_europeanwindowshosting 

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
    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 the temporary table  
       
    INSERT INTO @temp1 (EmployeeId, EmployeeName, EmployeeDesignation)  
         VALUES(1,'Peter','Software Engineer'),  
         (2,'Scott','Web Developer'),  
         (3,'Alexandria','Business Development Executive'),  
         (4,'Tom','Business Development Executive'),  
         (5,'Martin','Software Engineer')  
           
    -- Select all records from the 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 be described 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/create the stored procedure and insert data in table and also display the result in tabular format, for that here we will create another stored procedure and will call/execute the created 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 analyze the above procedure then you can see our first created stored procedure is executed with "EXEC." You  can also use "EXECUTE", where @Id, @Name, and @Designation send parameters to the first stored procedure.

    --Execute Second Procedure By Passing Parameters  
    EXEC Employee_GetData @id=1, @name='Peter', @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.

HostForLIFEASP.NET SQL Server 2019 Hosting