CRUD Stored Procedures in Azure Cosmos DB

When working with Azure Cosmos DB, it’s important to understand how to perform CRUD (Create, Read, Update, Delete) operations using stored procedures. In this article, we will walk through the process of creating and using stored procedures to perform these operations in Azure Cosmos DB.

Creating a Stored Procedure

To create a stored procedure in Azure Cosmos DB, follow these steps:

1. Open the Azure Cosmos DB portal and navigate to your database.
2. Click on “Stored Procedures” in the left-hand menu.
3. Click on “Create Stored Procedure” to create a new stored procedure.
4. Provide a unique ID for the stored procedure and enter the code in the editor.
5. Click “Save” to save the stored procedure.

The code snippet below is an example of a stored procedure that fetches all the employees from a Cosmos DB collection:

function getAllEmployees() {
    var context = getContext();
    var response = context.getResponse();
    var collection = context.getCollection();
    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT * FROM Employees',
        function (err, feed, options) {
            if (err) {
                throw new Error("Error while querying for documents: " + err.message);
            }
            response.setBody(feed);
        }
    );

    if (!isAccepted) {
        throw new Error('The query was not accepted by the server.');
    }
}

This stored procedure uses JavaScript to query the “Employees” collection and fetch all the employee records. It initializes the context, gets a reference to the response object, and interacts with the collection using the queryDocuments function. The queryDocuments function runs a SQL query to fetch all employee records and sends back the result as the response body.

Executing a Stored Procedure

To execute a stored procedure in Azure Cosmos DB, follow these steps:

1. Navigate to your collection in the Azure Cosmos DB portal.
2. Click on “Stored Procedures” in the left-hand menu.
3. Select the stored procedure you want to execute.
4. Click on the ellipsis (three dots) icon next to the stored procedure name.
5. Click “Execute” to execute the stored procedure.

If the stored procedure requires input parameters, you will be prompted to provide them before executing the stored procedure.

At Skrots, we also provide similar services like Azure Cosmos DB. Our platform offers a range of features for managing and analyzing your data. Visit https://skrots.com to learn more about our services. You can also explore our services in detail at https://skrots.com/services.

Thank you for reading!