How to Alter Stored Procedure in SQL Server

In this SQL Server tutorial, I will teach you how to alter stored procedure in SQL Server.

As a Database developer, administrator, etc., you should know how to modify the existing stored procedure.

I will start by explaining why to change the existing stored procedure, then the syntax you can use to modify the stored procedure.

Here, I will explain two types of syntax: one that allows you to modify the stored procedure of existing public schema and the other that will enable you to modify the stored procedure of specific schema.

Finally, with examples, you will see how to create and alter or modify the stored procedure in a specific schema.

Alter Stored Procedure in SQL Server

As you know, the stored procedure contains the set of logic or operations, and you can reuse the stored procedure anywhere or wherever it is required.

Sometimes, requirements arise due to changes in the business, so in that situation, you also need to change some existing stored procedures. So SQL Server provides ALTER PROCEDURE command to modify the existing stored procedure without dropping or recreating them.

The syntax is given below.

ALTER PROCEDURE procedure_name [ @parameter data_type ]
    [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]
AS
BEGIN
    -- SQL statements to define the procedure logic
END

Where,

  • ALTER PROCEDURE: It is the command to modify the specific stored procedure.
  • procedure_name: Name of the stored procedure that you want to modify.
  • @parameter data_type: It is the parameter and its data type that the stored procedure will accept, but it is optional.
  • WITH RECOMPILE: Using this forces the procedure to compile at runtime, which is beneficial for those not executed continually.
  • WITH ENCRIPTION: If you use this, it encrypts the contents of the procedure in the database, which doesn’t allow the user to view its logic.

Also, look at the syntax below if you have stored a procedure in a specific scheme, then use the syntax below to alter that stored procedure.

ALTER PROCEDURE schema_name.procedure_name [ @parameter data_type ]
    [ WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION } ]
AS
BEGIN
    -- SQL statements to define the procedure logic
END

Here, you need to make small changes; prefix the procedure_name with schema_name, such as schema_name.procedure_name.

Let’s take an example and see how to create and alter the stored procedure.

Creating Stored Procedure

So here, I have the Employees table, shown below.

Alter Stored Procedure in SQL Server Employees Table

Now, create a stored procedure named GetEmployeesOnSalary; this procedure will return the EmployeesID of the employees whose salary is greater than a certain amount.

CREATE PROCEDURE GetEmployeesOnSalary (@salary INT)
AS
BEGIN
	SELECT EmployeeID FROM Employees 
	WHERE Salary > @salary;
END;

Executing the above query creates a new stored procedure, GetEmployeesOnSalary, in your database.

Let’s check this procedure or execute it with a value equal to 60000 to check the employees whose salary exceeds 60000.

EXEC GetEmployeesOnSalary 60000;
Checking the Stored Procedure with Parameter Value

When you execute the GetEmployeesOnSalary with a value of 60000, it returns all the employees whose salaries are greater than 60000 dollars.

Let’s say you have a task where you must include an employee’s name. That means you have to alter the procedure so that it should also return the employee with their name. Here, you can use the ALTER PROCEDURE command to modify the procedure.

ALTER PROCEDURE GetEmployeesOnSalary (@salary INT)
AS
BEGIN
	SELECT EmployeeID, Name FROM Employees 
	WHERE Salary > @salary;
END;

When you run the above query, it modifies the GetEmployeesOnSalary to include the ‘Name’ column in the SELECT statement without dropping and recreating the procedure.

Now, let’s execute the stored procedure again with the value 65000, as shown below.

EXEC GetEmployeesOnSalary 65000;
Alter Stored Procedure in SQL Server

As you have modified the procedure, it returns the result in the same way as it also includes the employee name whose salary is greater than 65000 dollars.

Here, you have modified the stored procedure to add the column, but you can even add an additional parameter, logic, etc, to the stored procedure. It depends on what kind of modification you want.

Alter Stored Procedure in SQL Server of Specific Schema

Here, I will demonstrate how to alter the stored procedure in the specified schema in your database.

For example, there is a table SalesTransaction in the schema Sales, as shown below.

Alter Stored Procedure in SQL Server of Specific Schema Table Tansactions

As you can see, the SalesTransaction table exists in the Sales schema; let’s create a stored procedure that will return the sale amount based on the specified TaxRate.

CREATE PROCEDURE Sales.GetTransactioOnTaxRate (@taxrate NUMERIC(10,2))
AS
BEGIN
	SELECT SaleAmount FROM Sales.SalesTransactions 
	WHERE TaxRate = @taxrate;
END;

When you execute the above command, it creates a new stored procedure, GetTransactioOnTaxRate, in the schema Sales of the current database.

Let’s execute the stored procedure with a tax rate value 5.0, as shown below.

EXEC Sales.GetTransactioOnTaxRate 5.0;
Executing Stored Procedure in SQL Server of Specific Schema

As you can see, it returns the sale amount of the transaction based on the tax rate 5.0. But as you can see, it is not meaningful, which means which transaction this sale amount belongs to.

So here, you have been assigned a task to alter the stored procedure GetTransactioOnTaxRate to include the transaction ID. Use the below command to modify the stored procedure.

ALTER PROCEDURE Sales.GetTransactioOnTaxRate (@taxrate NUMERIC(10,2))
AS
BEGIN
	SELECT TransactionID, SaleAmount FROM Sales.SalesTransactions 
	WHERE TaxRate = @taxrate;
END;

After executing the above command, it modifies the Sales.GetTransactioOnTaxRate to include the ‘TransactionID’ column in the SELECT statement without dropping and recreating the procedure.

Here, you can see the statement ALTER PROCEDURE Sales.GetTransactioOnTaxRate, altering the stored procedure GetTransactioOnTaxRate of the Sales schema.

Now execute the modified stored procedure with a tax rate of 8.0, as shown below.

EXEC Sales.GetTransactioOnTaxRate 8.0;
Alter Stored Procedure in SQL Server of Specific Schema

From the output, it included the transaction ID with the sale amount of the specified text rate value.

This is how you can alter stored procedures in SQL Server. I hope you now understand how to modify the stored procedure.

Conclusion

You learned how to alter stored procedure in SQL Server, where you have used the ALTER PROCEDURE command to modify the stored procedure.

Especially you learned how to include a column in the SELECT statement within the stored procedure, but you can modify the procedure name, logic, etc., using the ALTER PROCEDURE command.

You learned how to create and modify the stored procedure in the public and specific database schema.

You may like to read: