How to Execute Stored Procedure for Update in SQL Server?

In this SQL Server tutorial, you will understand how to execute stored procedure for update in SQL Server.

You will create a new stored procedure for updating and then execute that procedure through the query or SQL Server Management Studio with real-world examples.

Also, you will learn to create a stored procedure that will allow you to update any of the tables in your database.

Execute Stored Procedure for Update in SQL Server

Before proceeding to execute the stored procedure for update in SQL Server. First, you must create a stored procedure for updating the data.

Suppose you table Products with columns ProductID, ProductName, Price, Description, and Stock, which is shown below.

Now, you have to create a stored procedure that can update the product price and stock. For that, use the below query.

CREATE PROCEDURE UpdatePriceStock

 @PriceValue DECIMAL(10,2),
 @StockValue INT,
 @ProdID INT

AS
BEGIN

 UPDATE Products 
 SET Price = @PriceValue, Stock = @StockValue
 WHERE ProductID = @ProdID;

END;
GO

In the above query, CREATE PROCEDURE creates an UpdatePriceStock stored procedure with input parameters such as @PriceValue of type DECIMAL(10,2), @StockValue of type INT, and @ProdID of type INT.

Then, under the BEGIN and END blocks, the UPDATE statement is used to update the product details, such as price and stock, based on the provided value through parameters.

Now update the Laptop product with a price equal to 1000 dollars and stock equal to 50 using the below query.

EXEC UpdatePriceStock 1000.00, 50, 2;
GO

In the above query, execute the UpdatePriceStock using the EXEC statement. Also, parameter values are provided, which are equal to 1000.00 for the price, 50 for stock, and 2 is the product ID on which the update applies

View the Products table for products with an ID equal to 2 using the below query.

You can see the updated details, such as price and stock for the laptop product with ID equal to 2.

Execute Stored Procedure for Update in SQL Server using SSMS

You can also execute the UpdatePriceStock stored procedure using the SQL Server Management Studio.

For that, open the SSMS and connect to the SQL Server instance where you have created the stored procedure in your database.

Then, expand the Database node from the Object Explorer panel, and after that, expand your database, which contains stored procedure (E_Commerce). Expand the Programmability folder and then expand the Stored Procedures subfolder, as shown below.

Under the Stored Procedures folder, you see the dbo.UpdatePriceStock procedure. To execute this stored procedure, right-click on it and select the option Execute Stored Procedure.

After choosing the option Execute Stored Procedure, a wizard of Execute Procedure appears.

After providing the value of the product with an ID equal to 2, click on the OK button; if you see the output as shown below, it means you have successfully executed the UpdatePriceStock stored procedure and updated the product price and stock value.

Let’s view the updated details of the product with an ID equal to 2 using the code below.

SELECT * FROM Products
WHERE ProductID = 2;
GO

Now you have the updated data for the laptop product that you can see in the above output. This is how to execute stored procedure for update in SQL Server.

Here, in the above examples, you created a stored procedure that updates only specific information such as price and stock of Products table. In other words, it updates the specific table data.

Execute Stored Procedure for Update in SQL Server for Any Table

But, now you need to create a stored procedure that can update the data of any table by providing the information.

For that, use the below query to create an UpdateTableData procedure.

CREATE PROCEDURE UpdateTableData
    @TableName NVARCHAR(128),
    @SetClause NVARCHAR(MAX),
    @WhereClause NVARCHAR(MAX)
AS
BEGIN

   DECLARE @SqlStatement NVARCHAR(MAX)

    
   SET @SqlStatement = 'UPDATE ' + @TableName +
                       ' SET ' + @SetClause +
                       ' WHERE ' + @WhereClause

   EXEC sp_executesql @SqlStatement
END
GO

The above query creates an UpdateTableData stored procedure using the CREATE PROCEDURE statement.

Three input parameters @TableName NVARCHAR(128), @SetClause NVARCHAR(MAX), and @WhereClause NVARCHAR(MAX) are defined within the procedure.

Here, the parameters allow you to specify the table name (@TableName) that you want to update, the value for columns (@SetClause), and the conditions (@WhereClause) for which records to update.

After that, within the BEGIN and END blocks @SqlStatement variable is declared, and this variable is going to store the dynamically generated SQL statement that will perform the update operation.

Then, the query part from the SET keyword constructs the dynamic SQL statement for updating the table column values.

• The ‘UPDATE’ + @TableName specifies the table that you want to update.
• ‘SET’ + @SetClause specifies what column value you want to update or want to set a new value for which column.
• ‘WHERE’ + @WhereClause specifies the conditions for which records you want to update.

To execute the dynamically generated SQL statement, the sp_executesql system procedure is used. In simple words, the sp_executesql executes the SQL statement stored in the @SqlStatement variable.

Now, you can use the UpdateTableData to update any table column information. For example, suppose you have a Users Table, which is shown below.

You have to update the username from Rodney to Rodney Roy with a user ID equal to 1. For that, you can execute the UpdateTableData procedure using the below query.

EXEC UpdateTableData
	@TableName = 'Users',
	@SetClause = 'UserName = ''Rodney Roy''',
	@WhereClause = 'UserID = 1';
GO

In the above query while executing the UpdateTableDate procedure, parameters with values are specified such as @TableName = ‘Users’, @SetClause ‘UserName = ”Rodney Roy”’, and @WhereClause = ‘UserID =1’;

As you execute the above query, it finds the user with an ID equal to 1 in the Users table and updates the name to Rodney Roy.

Using the UpdateTableData procedure, you can perform updatation on any table in your database.

View the Users table using the below query.

SELECT * FROM Users;
GO

You can see the updated user details for the user with an ID equal to 1. This is how to create and execute stored procedures for updating any table in SQL Server.

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. Also, you created a stored procedure that updates any table data in the current database.

You may also like:

• How to Execute Stored Procedure for Insert in SQL Server?

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.