Create stored procedures in Azure SQL database

This Azure SQL article will help you to get familiar with stored procedures in an Azure SQL database. We will explain how to create stored procedures in an Azure SQL database with the help of some examples.

• How to create stored procedure in Azure SQL database
• Create stored procedure template for Azure SQL database
• Azure SQL stored procedure example
• How to execute stored procedure in Azure SQL database
• Azure SQL stored procedures with parameters
• Create stored procedure in Azure SQL database with input parameters
• Create stored procedure in Azure SQL database with output parameters
• Azure SQL list stored procedures
• Modify stored procedure SQL Azure
• Azure SQL execute stored procedure permissions
• Azure SQL role execute stored procedure
• Send email from Azure SQL stored procedure
• Azure automation run SQL stored procedure
• Could not find stored procedure ‘sp_configure’
• Azure SQL could not find stored procedure ‘sp_msforeachtable’

How to create stored procedure in Azure SQL database

In this section, I will explain how to create a stored procedure in an Azure SQL database.

To create a stored procedure, we use the CREATE PROCEDURE statement. The syntax for creating a simple stored procedure without any parameters is written below:

Syntax:

CREATE PROCEDURE <procedure name>
AS
BEGIN
	<stored procedure code>
END

Let us see an example of a stored procedure.

Create stored procedure template for Azure SQL database

The following is the template for creating a stored procedure in an Azure SQL database:

-- =======================================================
-- Create Stored Procedure Template for Azure SQL Database
-- =======================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author, , Name>
-- Create Date: <Create Date, , >
-- Description: <Description, , >
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
(
    -- Add the parameters for the stored procedure here
    <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
    <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Insert statements for procedure here
    SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

• You can also generate this template if you have installed SQL Server management studio.
• In SQL Server management studio, connect to your Azure SQL database and expand the database in the object explorer window.
• Open the folder Programmability and then right click on Stored Procedures. Click on New >> Stored Procedure…

Read Migrate SQL Server to Azure SQL database

Azure SQL stored procedure example

We will create a simple stored procedure to display a message on the output screen.

• Firstly, connect to the Azure SQL database with an IDE like SQL Server management studio, Azure Data studio, VS code, etc. I will be creating this stored procedure from the Azure portal.
• In the Azure portal, go to your database and click on Query Editor in the left side naviagtion window.
• Login to the Azure SQL database using the login credentials.
• Write the following query in the query editor window.

CREATE PROCEDURE dbo.PrintRow
AS
BEGIN
	SELECT * FROM SalesLT.Customer
END

• This is a simple stored procedure named as dbo.PrintRow that will return a resultset of all the records in a table named SalesLT.Customer.

Now let us execute the stored procedure.

Read How to execute stored procedure in SQL Server

How to execute stored procedure in Azure SQL database

To execute a stored procedure, we use the EXEC or EXECUTE statement. The syntax for this statement is:

EXEC / EXECUTE <procedure name>

• Now we will execute the above created stored procedure. Write the execute as following and click on Run to execute the query.

EXEC dbo.PrintRow

• The output of this stored procedure will be:

• You can see that the rows of the specified table is displayed on the output screen.

Thus, you might have learned how you can create and execute a stored procedure in an Azure SQL database.

Azure SQL stored procedures with parameters

In this section, I will explain how you can create stored procedures with parameters in an Azure SQL database.

There are two types of parameters in an Azure SQL database:

1. Input parameters: These parameters are used to supply some values or data to a stored procedure. The stored procedure will use these values to perform some operations.
2. Output parameters: These parameters are used to return some data or results from the stored procedure.

You will see an example of each type of parameter in the below sections.

Read Azure sql password validation failed

Create stored procedure in Azure SQL database with input parameters

Sometimes we need to supply some values to a stored procedure so that it can perform some tasks depending on these values. In such a case, we can define some input parameters while creating a stored procedure.

The following is the syntax to define the input parameters while creating a stored procedure:

CREATE PROCEDURE <procedure name>
	<parameter1> <data type>,
	<parameter2> <data type>,
	.
	.
	<parameterN> <data type>
AS
BEGIN
	<Body of stored procedure>
END

While defining an input parameter, you also have to define the data type of the input parameter.

Now I will show you an example of a stored procedure with input parameters.

• Suppose we want to create a stored procedure that takes the customer ID of a customer and returns the column values of that customer. See the query below:

CREATE PROCEDURE dbo.ResultSet @CustomerID int
AS
BEGIN
	SELECT * FROM SalesLT.Customer WHERE CustomerID= @CustomerID
END

• In the above code, @CustomerID is the input parameter.
• We are using this input parameter inside the SELECT statement to return a row associated with that value.
• Now let us execute this stored procedure.

EXEC dbo.ResultSet @CustomerID= 10

• You can also execute the stored procedure without specifying the parameter names. But in that case, you have to pass the values in the same sequence as you have created parameters while defining the stored procedure. For example:

EXEC dbo.ResultSet 10

• You can see that the details of the specified customer is returned as a resultset.

In this way, you can create a stored procedure with input parameters.

Read SQL Server find text in stored procedure

Create stored procedure in Azure SQL database with output parameters

In this section, I will explain what are output parameters and how to create stored procedures with output parameters in the Azure SQL database. You will also see an example.

Sometimes your stored procedure performs some tasks or operations that return some result and you want that result value to use somewhere else. For example, using this value in another stored procedure.

In that case, you can use an output parameter to return the value from the stored procedure.

While creating the stored procedure, you have to specify the OUTPUT keyword after the parameter declaration.

The general syntax for creating a stored procedure with an output parameter is:

CREATE PROCEDURE <procedure name>
	<output prameter name> <data type> OUTPUT
BEGIN
	<body of stored procedure>
END

Let us understand with an example.

Consider a stored procedure that returns a specific value from a row in a table and we can use this value in a variable. We will return this value through an output parameter.

CREATE PROCEDURE dbo.ReturnFirstName
	@CustomerID int,
	@FirstName nchar(30) OUTPUT
AS
BEGIN
	SELECT @FirstName= [FirstName] FROM [SalesLT].[Customer] WHERE CustomerID= @CustomerID
END

• In the above stored procedure @CustomerID is the input parameter while @FirstName is the output parameter.
• Now let us execute the stored procedure. We will stored the value returned from the stored procedure inside a variable.

DECLARE
	@FirstName nchar(30)
EXEC dbo.ReturnFirstName 11, @FirstName OUTPUT
SELECT @FirstName

• In the above code, we have declared a variable named @FirstName that we are passing as an output parameter to the stored procedure. Later we are printing that result in the output.

• You can see in the output that the result is stored in the variable and printed in th output screen.

Thus, in this way you can use the output parameters in a stored procedure to return a value.

Read SQL Server stored procedure if exists update else insert

Azure SQL list stored procedures

In this section, I will explain how you can get a list of all stored procedures in your Azure SQL database.

There is a system stored procedure named sp_stored_procedures that you can use to list all the stored procedures in your Azure SQL database.

You can simply write the name of this stored procedure and execute it.

You can also execute the stored procedure with the EXEC or EXECUTE statement.

EXEC sp_stored_procedures

Hence, in this way you can get the list of all stored procedures in your Azure SQL database.

Read Loop in SQL Server stored procedure

Modify stored procedure SQL Azure

You may face a scenario when you want to make some changes to the functionality of a stored procedure after it has been created. In that case, you can modify the stored procedure using the ALTER PROCEDURE statement.

The general syntax for the ALTER PROCEDURE statement is:

ALTER PROCEDURE <procedure name>
	<parameter 1> <data type>,
	<parameter 2> <data type>,
	.
	.
	<parameter n> <data type>
AS
BEGIN
	<new body of the stored procedure>
END

Let us see an example. We will create a stored procedure and alter it later.

Suppose we have created a stored procedure named dbo.GetData that retrieves some data from a table named dbo.Persons.

CREATE PROCEDURE dbo.GetData
AS
BEGIN
	SELECT * FROM dbo.Persons
END

Now suppose you changed the name of the table or you want to fetch data from another table instead of this table. You will need to alter the stored procedure as:

ALTER PROCEDURE dbo.GetData
AS
BEGIN
	SELECT * FROM dbo.Employees
END

After executing the above query, the stored procedure dbo.GetData will now retrieve records from a table named dbo.Employees.

Thus, you might have learned how you can alter or modify a stored procedure in an Azure SQL database.

Read SQL Server stored procedure modified date

Azure SQL execute stored procedure permissions

When you create a stored procedure in an Azure SQL database, the other users may or may not have the privileges to execute the stored procedure.

If any other user or role in the database wants to execute that stored procedure, it must have the EXECUTE permission on the stored procedure. If you try to execute any stored procedure without EXECUTE permission, you will face an error.

In this section. I will explain how you can grant a user the EXECUTE permission on a stored procedure.

For example, if we have a stored procedure named dbo.PrintSum and we want to assign EXECUTE permission to a user named TestUser, we will write the T-SQL query as:

GRANT EXECUTE ON OBJECT::dbo.PrintSum  
    TO TestUser;  

Now if you log in as the TestUser, you can execute the stored procedure without any error. Similarly, you can assign any stored procedure permission to any user or role in the database.

Azure SQL role execute stored procedure

You might have faced some scenarios when you created a new role and you were not able to execute any stored procedure because of insufficient permissions.

After creating a new role, you need to manage permissions for that role. If a user with that role wants to execute the stored procedure, either the user or the role associated with that user must have EXECUTE permissions for the stored procedure.

To give EXECUTE permissions on a role on a stored procedure, you can use the GRANT statement.

For example, we have created a role named as customRole. If we want to give it the EXECUTE permission on a stored procedure named dbo.TestProcedure, we will write the query as:

GRANT EXECUTE ON OBJECT::dbo.TestProcedure
    TO customRole; 

Read Stored procedure for search functionality in SQL Server

Send email from Azure SQL stored procedure

You cannot send emails from an Azure SQL stored procedure. This is because, in an Azure SQL database, you do not have the sp_send_dbmail system stored procedure that you use to send emails in a SQL Server database.

In a SQL Server database, the sp_send_dbmail system stored procedure is stored in the msdb database and this database does not exist in any Azure SQL database.

However, you can create logic apps in Azure to send emails periodically.

Read Try catch in SQL Server stored procedure with examples

Azure automation run SQL stored procedure

In this section, I will explain how you can automate the execution of a stored procedure in an Azure SQL database.

Suppose you have a stored procedure that inserts data into a log table periodically e.g every hour or every day. You will create a stored procedure and schedule it according to your requirements. Let us understand with an example.

• We have a table named SalesLT.Customer storing the customer details of a company.
• We will create a table named dbo.CustomerLog into which we will make two columns:
  1. RowCount: Storing the total number of rows in the SalesLT.Customer table at a particular date and time
  2. DateTime: The time stamp when the row will be inserted into the dbo.CustomerLog table.

CREATE TABLE dbo.CustomerLog(
	[RowsCount] int,
	[DateTime] DateTime
)

• Now we will create a stored procedure named dbo.LogInto that will insert a row into the dbo.CustomerLog table.

CREATE PROCEDURE dbo.LogInto
AS
BEGIN
	DECLARE
		@RowCount int,
		@DateTime DateTime
	SELECT @RowCount= COUNT(*) FROM SalesLT.Customer
	SET @DateTime= SYSDATETIME()
	INSERT into dbo.CustomerLog( [RowsCount], [DateTime])
		VALUES( @RowCount, @DateTime)
END	

• We will schedule this stored procedure to execute once in a day.

You need to have an Azure automation account in order to automate the tasks. Follow the below section to create an Azure automation account.

Create Azure automation account

• Open the Azure portal and search for Automation Accounts. Click on Automation Accounts.

• Click on Create to create a new automation account.
• Enter the name of the automation account, subscription type to use, resource group name under which you want to create the account and the location of the server. Click on Create.

• Open your automation account and under the Process Automation tab, click on Runbooks.

• Click on Create a runbook to create a new runbook. Give the name of the runbook.
• Select the Runbook type as PowerShell.
• Give the description for the runbook if you want. It helps to know what kind of task will be performed by the runbook. Click on Create.

• Write the below code in the runbook editor:

Write-Output "Execution started"

# Instantiate the connection to the SQL Database

$server="mysql1000.database.windows.net"
$user= "azureadmin"
$password= "Kushal@123"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server = $server; Database = DemoDatabase; User ID = $user; Password =$password ;"
$sqlConnection.Open()

Write-Output "Azure SQL database connection opened"


# Create a new SQL command object

$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 60
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText= 'exec [dbo].[LogInto]'

# Execute the query to run the stored procedure

$result = $sqlCommand.ExecuteNonQuery()
Write-Output "Stored procedure execution completed"

# Close the SQL connection

$sqlConnection.Close()

Write-Output "Connection Closed"

• Remember to change the server information like the server name, user id, password, database name.
• Also, in the $sqlCommand.CommandText variable, write your query to execute the stored procedure.
• Click on Save. You can also click on Test pane where you can test if the runbook will be exceuted sucessfully or not. It is a good practcie to test the execution before scheduling the job.
• Once tested, click on Publish to publish the runbook. Now your runbook is ready to be scheduled.

Read How to create SQL authentication user in Azure SQL database

Schedule stored procedure in Azure SQL database

• In the runbook Overview, click on Link to schedule.

• In the next page, click on Link a schedule to your runbook.
• If you already created a schedule, you can link your runbook to this schedule. Otherwise, click on Add a schedule to create a new schedule.

• Specify the name, description, date and time, time zone, recurrence and the expiration of the new schedule.
• You can opt to run the runbook once or execute periodically by spcecifying the recurrence period.
• Also, you can set the expiry date after which the schedule will be expireda and the runbook will not be executed.
• Click on Create to create the schedule.
• Now the stored procedure will run automatically after the spcefied recurrence period. You can also change the recurrence period later if you wish to do so in the schdeule settings.

Thus, you might have learned how you can automate the execution of a stored procedure in an Azure SQL database.

Read Backup and restore SQL Server to Azure Blob storage

Could not find stored procedure ‘sp_configure’

You might have faced this error “Could not find stored procedure ‘sp_configure’” in Azure SQL database while trying to configure the server-level settings.

This is because the sp_configure system stored procedure is not available in the Azure SQL database. This system stored procedure is available in a SQL Server instance.

If you want to change the server-level settings, you have to figure out other options. Mostly, you will find these settings in the Azure portal.

Azure SQL could not find stored procedure ‘sp_msforeachtable’

You faced this error “Azure SQL could not find stored procedure ‘sp_msforeachtable’ “ because the stored procedure sp_msforeachtable is not available in an Azure SQL database.

This system stored procedure is stored in the master database of a SQL Server instance.

Related Posts:

• Cannot open backup device operating system error 50 azure
• How to rename a database in Azure SQL

In this article, I covered most of the topics related to the stored procedures in the Azure SQL databases. The following is the list of topics that I covered:

• How to create stored procedure in Azure SQL database
• Create stored procedure template for Azure SQL database
• Azure SQL stored procedure example
• How to execute stored procedure in Azure SQL database
• Azure SQL stored procedures with parameters
• Create stored procedure in Azure SQL database with input parameters
• Create stored procedure in Azure SQL database with output parameters
• Azure SQL list stored procedures
• Modify stored procedure SQL Azure
• Azure SQL execute stored procedure permissions
• Azure SQL role execute stored procedure
• Send email from Azure SQL stored procedure
• Azure automation run SQL stored procedure
• Could not find stored procedure ‘sp_configure’
• Azure SQL could not find stored procedure ‘sp_msforeachtable’