Tutorial Gateway

  • C
  • C#
  • Python
  • SQL
  • Java
  • JS
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs
  • MySQL

UPDATE Stored Procedure in SQL Server

How to write the UPDATE Statements inside the SQL Stored Procedure?. Or How to write UPDATE Stored Procedure in SQL Server with an example.

For this SQL Server UPDATE Stored Procedure demonstration, we are going to use the below table. I suggest you refer Introduction to Stored Procedures article to understand the basics.

UPDATE Stored Procedure in SQL Server 1

UPDATE Stored Procedure in SQL Server Example

In this example, we will show how to use the UPDATE Statement inside the Stored procedure. Please refer to Introduction to Stored Procedure article.

-- 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 SQL 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

UPDATE Stored Procedure in SQL Server 2

Use the EXEC Command (Execute Command) to execute the stored procedure

EXEC [dbo].[UpdateStoredProcedureFirstExample];
GO
UPDATE Stored Procedure in SQL Server 3

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

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
UPDATE Stored Procedure in SQL Server 4

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.

As you can see, 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
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
UPDATE Stored Procedure in SQL Server 5

Let us use the EXEC Command to execute the stored procedure

EXEC [dbo].[UpdateStoredProcedureSecondExample]
GO
UPDATE Stored Procedure in SQL Server 6

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

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
  ORDER BY [YearlyIncome] DESC
UPDATE Stored Procedure in SQL Server 7

UPDATE Statements With Parameter in SQL Stored Procedure

This example shows how to create the update Stored procedures with parameters.

-- Example for UPDATE Statement within the Stored Procedure in SQL Server
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
UPDATE Stored Procedure in SQL Server 8

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

EXEC [dbo].[UpdateStoredProcedureThirdExample] @Occupation = N'Admin';
GO
-- Or you can write
EXEC [dbo].[UpdateStoredProcedureThirdExample] N'Admin';
GO
UPDATE Stored Procedure in SQL Server 9

Let us see the inserted data

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
  ORDER BY [YearlyIncome] DESC
UPDATE Stored Procedure in SQL Server 10

Update Statements With Multiple Parameter in a 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
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
UPDATE Stored Procedure in SQL Server 11

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

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
UPDATE Stored Procedure in SQL Server 12

Use the below-shown query to check the UPDATE Stored Procedure result.

SELECT [ID]
      ,[FirstName]
      ,[LastName]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [EmployeeDup]
  ORDER BY [Sales] DESC
UPDATE Stored Procedure in SQL Server 13

Filed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021ยท All Rights Reserved by Suresh.
About | Contact | Privacy Policy