UPDATE Stored Procedure in SQL Server

In this article, we will show you, How to write the UPDATE Statements inside the SQL Stored Procedure, Or How to write UPDATE Stored Procedure in SQL Server with an example.

I suggest you refer Introduction to Stored Procedures in SQL Server article to understand the basics of the stored procedure. For this demonstration, We are going to use the below table

UPDATE Stored Procedure in SQL Server Example

In this example, we will show you, How to use the SQL UPDATE Statement inside the Stored procedure

-- Example for UPDATE Statement within the Stored Procedure in SQL Server
USE [SQL Tutorial]
GO

IF OBJECT_ID ( 'UpdateStoredProcedureFirstExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE UpdateStoredProcedureFirstExample;  
GO

CREATE PROCEDURE UpdateStoredProcedureFirstExample

AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [EmployeeDup] SET [LastName] = N'Tutorial Gateway',
	                         [Occupation] = N'Management'
	 
END
GO

From the above code snippet, you can see that, we are updating the Last Name as Tutorial gateway, and Occupation as Management for all the records present in the EmployeeDup table

OUTPUT

Let me use the EXEC Command (Execute Command) to execute the stored procedure

USE [SQL Tutorial]
GO

EXEC [dbo].[UpdateStoredProcedureFirstExample];
GO

OUTPUT

Now, let’s see whether the execution of the stored procedure Updated the Last Name and Occupation in our EmployeeDup table or not

USE [SQL Tutorial]
GO

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]

OUTPUT

UPDATE Stored Procedure in SQL Server with WHERE Clause

In this example, we will show you, How to use the WHERE Clause, along with the UPDATE Statement inside the Stored procedure.

From the below code snippet you can see that, the procedure will update the Last Name as Gateway Tutorial, and Occupation as Admin for all the records present in the EmployeeDup table whose Yearly Income is greater than or equal to 70000.

-- Example for UPDATE Statement within the Stored Procedure in SQL Server
USE [SQL Tutorial]
GO

IF OBJECT_ID ( 'UpdateStoredProcedureSecondExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE UpdateStoredProcedureSecondExample;  
GO

CREATE PROCEDURE UpdateStoredProcedureSecondExample

AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [EmployeeDup] SET [LastName] = N'Gateway Tutorial',
	                         [Occupation] = N'Admin'
        WHERE [YearlyIncome] >= 70000
	 
END
GO

OUTPUT

Let us use the EXEC Command to execute the stored procedure

USE [SQL Tutorial]
GO

EXEC [dbo].[UpdateStoredProcedureSecondExample]
GO

OUTPUT

Let us run the following query and see whether the Stored Procedure updated the records or not

USE [SQL Tutorial]
GO

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
  ORDER BY [YearlyIncome] DESC

OUTPUT

UPDATE Statements With Parameter in SQL Stored Procedure

In this example, we will show you, How to create the update Stored procedures with parameters.

-- Example for UPDATE Statement within the Stored Procedure in SQL Server
USE [SQL Tutorial]
GO

IF OBJECT_ID ( 'UpdateStoredProcedureThirdExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE UpdateStoredProcedureThirdExample;  
GO

CREATE PROCEDURE UpdateStoredProcedureThirdExample
        @Occupation VARCHAR(50)
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [EmployeeDup] SET [YearlyIncome] = 110000
    WHERE [Occupation] = @Occupation	 
END
GO

OUTPUT

Let me execute the stored procedure. As you can see, we had the @Occupation parameter. So, let us pass the parameter value using any of the following ways

USE [SQL Tutorial]
GO

EXEC [dbo].[UpdateStoredProcedureThirdExample] @Occupation = N'Admin';
GO
-- Or you can write
EXEC [dbo].[UpdateStoredProcedureThirdExample] N'Admin';
GO

OUTPUT

Let us see the inserted data

USE [SQL Tutorial]
GO

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
  ORDER BY [YearlyIncome] DESC

OUTPUT

Update Statements With Multiple Parameter in SQL Stored Procedure

In this example, we will use the Multiple parameters along with the Update statement inside the stored procedure.

-- Example for UPDATE Statement within the Stored Procedure in SQL Server
USE [SQL Tutorial]
GO

IF OBJECT_ID ( 'UpdateStoredProcedureFourthExample', 'P' ) IS NOT NULL   
    DROP PROCEDURE UpdateStoredProcedureFourthExample;  
GO

CREATE PROCEDURE UpdateStoredProcedureFourthExample
        @Occupation VARCHAR(50),
		@Sales FLOAT
AS
BEGIN
	SET NOCOUNT ON;
	UPDATE [EmployeeDup] SET [FirstName] = N'SQL Tutorial',
	                         [YearlyIncome] = 150000
    WHERE [Occupation] = @Occupation OR [Sales] >= @Sales	 
END
GO

OUTPUT

Following are the number of ways you can execute the stored procedure. Let me execute the sp.

T-SQL CODE

USE [SQL Tutorial]
GO

EXEC [dbo].[UpdateStoredProcedureFourthExample] @Occupation = N'Management', @Sales = 2500;
GO
--OR You Can Write
EXEC [dbo].[InsertStoredProcedureFifthExample] @Sales = 2500, @Occupation = N'Management';
GO

--OR You Can Write
EXEC [dbo].[InsertStoredProcedureFifthExample] N'Management', 2500;
GO

OUTPUT

Use the below-shown query to check the result.

USE [SQL Tutorial]
GO

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
  ORDER BY [Sales] DESC

OUTPUT

Thank you for Visiting Our Blog