Alter Stored Procedure in SQL Server

In this SQL Server tutorial, we will learn how to alter stored procedure in SQL Server. And we will also discuss the following list of topics.

• Alter stored procedure in SQL Server
• Alter stored procedure in SQL Server Example
• Alter stored procedure in SQL Server using SQL Server Management Studio
• Alter stored procedure permission in SQL Server
• Alter stored procedure name in SQL Server
• Alter stored procedure syntax in SQL Server
• Create or alter stored procedure in SQL Server
• Modify stored procedure name in SQL Server
• Alter query for stored procedure in SQL Server

Alter stored procedure in SQL Server

In this section, we will learn how to modify or alter an existing stored procedure in SQL Server. Consider, we had created a stored procedure using CREATE PROCEDURE statement, and now, we need to modify that stored procedure.

Now, to modify or alter a stored procedure in SQL server 2019, we need to use the ALTER PROCEDURE statement. And then, we need to define the modified definition of the stored procedure similar to how we defined it while creating.

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

Check out, What is a stored procedure in sql server

Alter stored procedure syntax in SQL Server

Now, let’s take a look at the syntax of using the ALTER PROCEDURE statement in SQL Server. The syntax for this is given below.

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 is used to define the name of the schema and procedure_name is used to define the name of the existing procedure that we want to modify.
• After this, @parameters are used to define the parameters for the stored procedure. Now, a parameter can an INPUT or OUTPUT parameter. Moreover, a maximum of 2,100 parameters can be specified in a stored procedure.
• And then, the BEGIN and END statement is used to define the block of SQL statement. Or we can also refer to it as a body of the stored procedure.

Alter stored procedure in SQL Server Example

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

USE [sqlserverguides]
GO

CREATE PROC [dbo].[AllStudents]
AS
BEGIN
	SELECT * FROM dbo.Students
END

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

USE [sqlserverguides]
GO

ALTER PROC [dbo].[AllStudents]
AS
BEGIN
	SELECT * FROM dbo.Students
	SELECT COUNT(1) AS [Total Count] FROM dbo.Students
END

Now, in the above example, we have used the ALTER PROC statement to alter the AllStudents table. And this time, we have added one more query in the body of the procedure. And this query will return the count of records that are there in the table.

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

Read How to execute stored procedure in SQL Server

Alter stored procedure in SQL Server using SQL Server Management Studio

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 by using the SQL Server Management Studio. And 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.

• 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.

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

Read SQL Server stored procedure if exists update else insert

Alter stored procedure name in SQL Server

Alter stored procedure name simply means renaming an existing stored procedure in SQL Server.

Renaming a stored procedure is similar to how we rename any other file in our system. So, we can use some other name to refer to the stored procedure in SQ Server.

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, a stored procedure needs to have ALTER PROCEDURE permission. So, in this section, we will understand how to check and grant ALTER PROCEDURE permission of a stored procedure to a user.

First, let’s understand how to check for ALTER PROCEDURE permission for a user in SQL Server. For this task, we can use the following syntax.

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.

And we are executing the query as a user whose name we have to define in place of user_name. Let’s take an example for this implementation.

USE [sqlserverguides]
GO

EXECUTE AS user = 'my_user'
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 then it is represented by “1” for that procedure. And if the user does not have permission then 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. And this task is possible in 2 ways.

1. By using SQL Server Management Studio
2. By using Trsanct-SQL

Using SQL Server Management Studio

• 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 Procedures.
• 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. For this, click on Search button and it 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 permiision to that user or role.

Using Trsanct-SQL

Now, in this section, we will learn how to grant ALTER PROCEDURE permission to a user using a query. And for this task, we will use the following syntax.

USE database;   
GRANT ALTER ON OBJECT::procedure_name
    TO username;  
GO  

In the above syntax, we are using the GRANT statement to grant the ALTER permission of an object to a user. And in this case, the object is a stored procedure. Now, let’s take a look at an example of this implementation.

USE [sqlserverguides];   
GRANT ALTER ON OBJECT::[dbo].[AllEmployees]
    TO my_user;  
GO  

In the example, we are using the syntax to grant the ALTER permission of a stored procedure to my_user. The name of the stored procedure is AllEmployees and it is stored in the sqlserverguides database.

Read SQL Server find text in stored procedure

Create or alter stored procedure in SQL Server

In SQL Server, we also have a CREATE OR ALTER statement and it is a combination of both CREATE as well as ALTER statement. By using this statement, we don’t need to write separate code to check the existence of a stored procedure in SQL Server.

So, usually, we need to create a stored procedure in such a way that if the procedure already exists then it gets modified. And if it does not exist then a stored procedure gets created.

Now, this CREATE OR ALTER statement simplifies our task as by using this we don’t have to write any separate logic.

So, by using this statement, if the stored procedure already exists then the ALTER statement will be used to modify that procedure. And if the procedure does not exist then CREATE statement will be used to create a new one.

Let’s understand its usability with the help of an example and for this, we are using the following query.

USE [sqlserverguides]
GO

CREATE OR ALTER PROCEDURE [dbo].[GetEmployeesByDept]
( @Dept VARCHAR(20) )
AS
BEGIN
	SELECT Name, gender, Department FROM dbo.Employees
	WHERE Department = @Dept
END
GO

In the above example, we are using CREATE OR ALTER statement to create a simple stored procedure. And this procedure will fetch records of a particular department from a table based upon the given department name.

You may also like the following sql server tutorials:

• Could not find stored procedure in SQL Server
• Loop in SQL Server stored procedure
• Drop stored procedure SQL Server
• How to see view definition in SQL Server
• SQL Server stored procedure modified date
• Stored procedure for search functionality in SQL Server
• Try catch in SQL Server stored procedure with examples

So, in this tutorial, we have learned how to alter stored procedure in SQL Server. And we have also discussed the following list of topics.

• Alter stored procedure in SQL Server
• Alter stored procedure in SQL Server Example
• Alter stored procedure in SQL Server using SQL Server Management Studio
• Alter stored procedure permission in SQL Server
• Alter stored procedure name in SQL Server
• Alter stored procedure syntax in SQL Server
• Create or alter stored procedure in SQL Server
• Modify stored procedure name in SQL Server
• Alter query for stored procedure in SQL Server