ALTER Stored Procedure in SQL Server

A few weeks ago, I created a stored procedure in SQL. Later, I need to update the item based on the stored procedure. To do this, we can use the ALTER Procedure in the stored procedure.In this SQL Server tutorial, I will show you how to ALTER stored procedure in SQL Server in detail. Also, I will show how we can ALTER in SQL server management studio.

Table of Contents

Alter Stored Procedure in SQL Server

Consider that we created a stored procedure using the CREATE PROCEDURE statement, and now, we need to modify that stored procedure.

So, the CREATE PROCEDURE and ALTER PROCEDURE statements work in the same manner. The main difference between them is that the CREATE PROCEDURE statement is used for new procedures, while the ALTER PROCEDURE statement works for existing procedures.

Syntax

Now, look at the syntax of using the ALTER PROCEDURE statement in SQL Server.

ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]   
    [ { @parameter [ type_schema_name. ] data_type }   
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]  
    ] [ ,...n ]   
[ WITH <procedure_option> [ ,...n ] ]  
[ FOR REPLICATION ]   
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }  
[;]

Let’s understand some main arguments from the syntax of ALTER PROCEDURE.

To modify a stored procedure, we can either use ALTER PROCEDURE or ALTER PROC statement. Both statements are the same.
The schema_name defines the schema’s name, and the procedure_name defines the name of the existing stored procedure that we want to modify.
After this, @parameters are used to define the parameters for the stored procedure. Now, a parameter can be an INPUT or OUTPUT parameter. Moreover, a stored procedure can specify a maximum of 2,100 parameters.
Then, the BEGIN and END statement is used to define the block of the SQL statement. We can also refer to it as a body of the stored procedure.

Create and ALTER Stored Procedure in SQL Server

Now we have to create and alter stored procedure SQL Server.

CREATE PROCEDURE My_Proc 
    @first_name varchar(20),
    @last_name  varchar(20)
AS
BEGIN
    
 SET NOCOUNT ON;

SELECT @first_name , @last_name;
END
GO

----To Execute-----
EXEC My_Proc 'Jesica', 'julie';

Create and Alter Stored Procedure in SQL Server

To Alter the created Stored Procedure, use the below query.

ALTER PROCEDURE My_Proc
    @first_name varchar(20),
    @last_name  varchar(20)
AS
BEGIN
    
    SET NOCOUNT ON;

    
    SELECT  @first_name + ', ' + @Last_name;
END

Exec  My_Proc 'Jesica', 'julie';

Approach 1: Using Count()

Let’s understand the usage of the ALTER PROCEDURE statement using a simple example. For this example, we are going to use the PO stored procedure. The code for this is as follows.

PO -> Purchase Order. You can keep the procedure name whatever you want.

CREATE PROC PO
AS
BEGIN
SELECT * FROM PurchaseOrder
END

Next, we will try to modify this stored procedure using the following query in SQL Server.

ALTER PROC PO
AS
BEGIN
	SELECT * FROM PurchaseOrder
	SELECT COUNT(1) AS [Total Count] FROM PurchaseOrder
END

In the above example, we used the ALTER PROC statement to alter the PurchaseOrder table. This time, we added one more query in the procedure’s body. This query will return the number of records in the table.

Now, after modification, if we execute the procedure, we will get the following output.

Approach 2: Using SSMS

In the previous section, we understood the syntax and usage of the ALTER PROCEDURE statement. But, there is another way using which we can generate the ALTER PROC statement for a selected procedure.

This implementation is possible using SQL Server Management Studio. Here are the steps for this implementation.

First, open SQL Server Management Studio and connect to the Database Engine.
After this, navigate to the Object Explorer and expand the database where the stored procedure was created.
Next, expand the Programmability directory and then expand the Stored Procedures directory.
Now, from the list of procedures, select and right-click a stored procedure and click on Modify. This will generate the ALTER PROCEDURE script for that procedure in a new editor window.

Alter stored procedure in SQL Server using SSMS

Now, we can easily modify the script of that stored procedure. Moreover, we can also confirm the syntax by using the Parse option from the Menu bar.

Parse option in SQL Server Management Studio

After making all the modifications to the stored procedure, we need to click on Execute option. And with this, our stored procedure will be modified.

Alter Stored Procedure Name in SQL Server

Renaming a stored procedure is similar to renaming any other file in our system. Therefore, we can refer to the stored procedure in SQL Server using another name.

We already have a detailed article on How to rename a stored procedure in SQL Server. And we have discussed different ways to rename a stored procedure and what limitations that we need to face.

Alter Stored Procedure Permission in SQL Server

In SQL Server, to modify or alter any stored procedure, it needs to have ALTER PROCEDURE permission. In this section, we will understand how to check and grant ALTER PROCEDURE permission to a user.

First, let’s understand how to check for ALTER PROCEDURE permission for a user in SQL Server. The following syntax can be used for this task.

USE database
GO

EXECUTE AS user = 'user_name'
SELECT USER_NAME() AS [User Name];

SELECT NAME, 
    has_perms_by_name(NAME, 'OBJECT', 'ALTER') as [ALTER Permission]
FROM sys.procedures

In the above syntax, we are using the SELECT statement to query the sys.procedures. The sys.procedures is an object catalog view, and it stores data related to procedures.

We execute the query as a user whose name we have to define in place of dbo. See the below example.

EXECUTE AS user = 'dbo'
SELECT USER_NAME() AS [User Name];
SELECT NAME, 
    has_perms_by_name(NAME, 'OBJECT', 'ALTER') as [ALTER Permission]
FROM sys.procedures

In the above example, we are checking the ALTER permission for all the stored procedures in the sqlserverguides database. And we are checking the permissions for a user whose name is my_user.

Now, the query will return the following result.

From the result, we can observe that if the user has ALTER permission, it is represented by “1” for that procedure. If the user does not have permission, it is represented by “0.”

After checking the permissions, it’s time to understand how to grant the ALTER PROCEDURE permission to a user in SQL Server. This is possible by using SQL Server Management Studio.

ALTER Stored Procedure Permission Using SSMS

In SQL Server Management Studio, first, connect to a Database Engine and then expand that Database instance.
After this, expand the required database and then first expand Programmability and after that expand Stored Procedure.
Next, right-click the required stored procedure and click on Properties. It will open a new Stored Procedure Properties window.

Now, in the Stored Procedure Properties window open the Permissions page.
Next, we need to select the user or role for which we want to grant the ALTER permission. To do this, click on Search, which will open another Select Users or Roles window.
In the new window, click on Browse to select a user or role, and then click on OK.
Now, under Users or Roles, first select the user, and then, under Permissions, tick mark the Grant option for ALTER permission.

In the end, click on OK to grant the ALTER permission to that user or role.

Conclusion

I hope you got clarity related to creating and altering stored procedures in SQL Server. It also covered how to alter Stored procedure permission in SQL Server Management Studio.