How to Execute the Stored Procedure for Updating the SQL Server?

A week ago, while creating the stored procedure, my team required me to update a record in the table using the same. I updated it using the stored procedure concept. Here, I am sharing my real-time experience of how I did in my organization.

Here, we will create a new stored procedure for updating and then execute that procedure through the query and through SQL Server Management Studio with examples.

Let’s get started to know more.

Table of Contents

Execute Stored Procedure for Update in SQL Server

Ensure you have created the stored procedure for the table before executing a stored procedure for an update in SQL Server.

For example, here I have an EmployeeInfo table with the columns EmployeeId, Employeename, EmployeeRole, Employeecountry and Employeesalary. Here, we can update the Employee’s salary and Employeename using the stored procedure concept.

Now, use the below query to update the record in the SQL Server table details. Here I want to modify Employeename and Employeesalary of EmployeeId 1. Use the below code and see the result.

Create Procedure up_UpdateEmpI
@EmployeeId int,
@Employeename varchar(20),
@EmployeeRole varchar(20),
@Employeecountry char(20),
@Employeesalary int
As
Begin
  UPDATE EmployeeInfo SET Employeename = @Employeename, EmployeeRole = @EmployeeRole, Employeesalary =@Employeesalary where EmployeeId =  @EmployeeId
End

Execute  up_UpdateEmpI 1, 'DavidPeter', 'HR', 'Australia',59000

This is how we have to execute the stored procedure for an update in the table by SQL query.

Execute Stored Procedure for Update in SSMS

We have previously seen how to execute the stored procedures for updating details in the table through SQL Query. Now, we will see how to execute them through SQL Server Management Studio.

Open the SSMS and connect to the SQL Server instance where you have created the stored procedure in your database.

Expand the Database and click step by step as per the below image.

Here Execute – Your Stored Procedure name page will open. Give the values to update and click OK.

Once you click OK, a updated details will be changed in the particular table. Use the below code to see the table.

Select * from Tablename

or

select * from Tablename
where EmployeeId = 2;


Select * from EmployeeInfo

How to Execute stored procedure for update

Here, I have updated the Employeename and Employeesalary from EmployeeId 2 in the EmployeeInfo table.

Execute Stored Procedure for Update One Value

For example, if the user wants to update a salary value only, then it is also possible in SQL Server Management Studio.

I updated the salary value only in the EmployeeInfo table for EmployeeId 3, and you can see the result below.

Execute stored procedure for Update in the SQL Server

Now, you can see the output below.

Execute Update Stored Procedure for the SQL Table

Execute Stored Procedure for Update Without Parameter

Here we will create a value with the stored procedure and execute the result.

You can also use SET NOCOUNT ON or avoid. The main purpose of using SET NOCOUNT ON is to improve the performance and efficiency of SQL Server queries and stored procedures.

CREATE PROCEDURE My_Procedure2 
    @name varchar(20),
    @Lastname  varchar(20),
    @Age int
AS
BEGIN
   
    SET NOCOUNT ON;

   

    SELECT @name , @Lastname , @Age;
END
GO


EXEC My_Procedure2 'Peter', 'Thomson' , 21;

This is how, we have to update and execute in SQL Server.

Execute Stored Procedure with Multiple Parameter

We can execute the stored procedure with multiple parameters for the update. Here, I am updating the EmployeeRole and Employeecountry in the EmployeeId 2.

CREATE PROCEDURE Example
        @EmployeeRole VARCHAR(50),
		@Employeecountry char(20)
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE EmployeeInfo SET EmployeeRole = 'TL',
	                         Employeecountry = 'France'
    WHERE EmployeeId = 2 
END
GO

EXEC [dbo].[Example] 'TL', 'France';
GO

select * from EmployeeInfo

Update statement with Multiple Parameter

Conclusion

In this SQL Server tutorial, you have covered how to execute stored procedures for updating data using the query and SQL Server Management Studio. We have also seen how to create a stored procedure that updates any table data in the current database.