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 example. I suggest you to 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
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- 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 presnt in the EmployeeDup table
OUTPUT
Let me use the EXEC Command (Execute Command) to execute the stored procedure
T-SQL CODE
|
1 2 3 4 5 |
USE [SQL Tutorial] GO EXEC [dbo].[UpdateStoredProcedureFirstExample]; GO |
OUTPUT
Now, lets see whether the execution of the stored procedure Updated the Last Name, and Occupation in our EmployeeDup table or not
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 |
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.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- 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
T-SQL CODE
|
1 2 3 4 5 |
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
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- 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
T-SQL CODE
|
1 2 3 4 5 6 7 8 |
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
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 |
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.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- 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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 inserted result.
T-SQL CODE
|
1 2 3 4 5 6 7 8 9 10 11 |
USE [SQL Tutorial] GO SELECT [ID] ,[FirstName] ,[LastName] ,[Occupation] ,[YearlyIncome] ,[Sales] FROM [EmployeeDup] ORDER BY [Sales] DESC |
OUTPUT
Thank you for Visiting Our Blog
Leave a Reply