Write, deploy, and call a stored procedure

To design and implement native applications using Microsoft Azure Cosmos DB, it is essential to have a good understanding of stored procedures. Stored procedures allow you to encapsulate complex database logic and execute it directly on the server side, resulting in improved performance and reduced network usage.

What is a Stored Procedure?

A stored procedure is a piece of precompiled, reusable code that is stored in the database. It can be written in JavaScript using the JavaScript syntax supported by Azure Cosmos DB. Stored procedures operate on documents in the collection and can be used to create, update, delete, or query data.

Writing a Stored Procedure

To write a stored procedure, you need to use the Azure Cosmos DB SQL API. Here’s an example of a simple stored procedure that inserts a document into a collection:

function insertDocument(documentToInsert) {
var collection = getContext().getCollection();
var isAccepted = collection.createDocument(collection.getSelfLink(), documentToInsert,
function(error, documentCreated) {
if (error) throw new Error("Error: " + error.message);
getContext().getResponse().setBody(documentCreated.id);
});

if (!isAccepted) getContext().getResponse().setBody("Document was not accepted.");
}

In the above example, we define a function insertDocument that takes a documentToInsert parameter. The getContext().getCollection() method retrieves the current collection, and collection.createDocument() creates a new document within that collection. The getContext().getResponse().setBody() method sets the response body, which in this case is the ID of the newly created document.

Deploying a Stored Procedure

Now that we have written our stored procedure, we need to deploy it to Azure Cosmos DB. Follow these steps to deploy the stored procedure using the Azure portal:

1. Open the Azure portal and navigate to your Cosmos DB account.
2. Select the desired Cosmos DB account and click on “Data Explorer” in the left-hand menu.
3. Choose the collection where you want to deploy the stored procedure.
4. Click on the “Stored procedures” tab, then click on “Create Stored Procedure”.
5. Provide a unique ID for the stored procedure and paste the JavaScript code into the editor.
6. Click “Save” to deploy the stored procedure.

Calling a Stored Procedure

Once the stored procedure is deployed, you can call it from your application. Here’s an example of how to call the insertDocument stored procedure we defined earlier using the Azure Cosmos DB .NET SDK:

using Microsoft.Azure.Cosmos;

In the above example, we use the ExecuteStoredProcedureAsync method to call the stored procedure. We provide the stored procedure ID, partition key value, and the parameters required by the stored procedure.

Conclusion

Stored procedures in Azure Cosmos DB are an excellent way to optimize database operations and improve application performance. They allow you to encapsulate complex logic on the server side, reducing network round trips and executing code closer to the data.

In this article, we learned how to write, deploy, and call a stored procedure in Azure Cosmos DB. Remember to refer to the official Microsoft documentation for more information and explore the various capabilities of stored procedures in Azure Cosmos DB.