Creating and Executing Azure CosmosDB Stored Procedures

   

     There are many ways to query the CosmosDB, Stored Procedures is an interesting way to query the CosmosDB Containers. When I saw the "Stored Procedure" option first time for CosmosDB, I taught that they were structured just like SQL Server stored procedures. I was wrong!

     Rather than using TSQL statements you need to use Javascript in CosmosDB Stored Procedures. How many DBA knows Javascript? Not that many! Are they willing learn Javascript to write/debug stored procedures in CosmosDB? My answer to that question is, Nope I don’t think so. If you are a DBA and you want to learn Javascript, Kudos to you!

      Stored Procedures executions are Atomic just like SQL Server, either all operations will get succeeds or all of them fail. Stored Procedures are useful if you need to do complex operations. If you use stored procedures, you will save CPU work since they are already pre-compiled. I am going to create a stored procedure by using the Azure Portal in the following example. You can do this by using SDK. I just didn’t feel comfortable generating database objects from SDK.

      Now, Let's create a stored procedure. First, here what my JSON object looks like in my container. OrderId is my primary key, CustomerId is my partition key.

        Just to keep things simple, In my awesome stored procedure, I want to select OrderId, and Customer Name by passing OrderId as parameter.  So in TSQL world it should looks like this.

SELECT OrderId, CustomerName FROM MyTable WHERE OrderId = @orderid


        On the Azure Portal or the Emulator, pick the collection you like to create a stored procedure for. I am using the CosmosDB Emulator in this example

       Click on New Stored Procedure then you will see a sample stored procedure. This example is complex for beginners. So just delete all of it for now. Stored procedure Id is the name of the stored procedure, I am going to call this stored procedure FindCustomerByOrderId

          Let's look at that Select statement again. We need to write this in Javascript.

SELECT OrderId, CustomerName FROM MyTable WHERE OrderId = @orderid


        I am going to use Filter function which works like WHERE clause then I am going to use Map function which should take specific columns from the JSON document. Since we are using two functions, we need to use the chain() function. Chain() function must be terminated with values() function. Check out the Javascript API page for more information for these and other functions.

function example(prefix){ 
        var collection = getContext().getCollection();  
        collection.chain().filter(function(doc){ 
           return doc.OrderId = prefix; 
        }).map(function(doc){
               return { 
                         id: doc.OrderId, 
                         CName: doc.Customer.CustomerName 
                       } }).value(); 
}

     
      Now, I am going to click on Save and check if I can see my new stored procedure under Stored Procedures of my collection.

      I am ready to execute my stored procedure now. Simply, just click on Execute button to start execution process. We need to pass Partition key and a parameter to our brand new stored procedure. CosmosDB asks these information when I clicked on Execute.

 
      CustomerId is my partion key so I passed its value as my partition key value. I changed the Type to Custom since OrderId is integer, I passed value 4 for the first parameter. Then, I clicked on Execute in the Input Parameter box. Here is the results.