CODEWITHSUNDEEP
azurestored-proceduresazure-cosmosdb
Ivan P.
Ivan P.

Reputation: 265

How to Execute an Existing Stored Procedure in DocumentDB?

I want to create a stored procedure in DocumentDB and use it whenever I need later on. To execute a stored procedure I need to know a storedProcedureLink. When I register stored procedure with CreateStoredProcedureAsync method I get the link, am I supposed to store this link somewhere myself if I want to execute this stored procedure later? Can I execute a stored procedure if all I know is a procedure name? From all the examples I found it seems that I need to create and register stored procedure right before I need to execute it, is it the case?

Upvotes: 3

Views: 6166

Answers (5)

Papa Stahl
Papa Stahl

Reputation: 887

It appears that Microsoft is now providing a few samples on how to execute stored procedures: https://learn.microsoft.com/en-us/dotnet/api/microsoft.azure.documents.client.documentclient.executestoredprocedureasync?view=azure-dotnet

Upvotes: 0

Guy Assaf
Guy Assaf

Reputation: 971

You can look for the SP in the DB first in .NET SDK

StoredProcedure storedProcedure = Client.CreateStoredProcedureQuery(GetCollection(eColl).SelfLink).Where(c => c.Id == "SP name").AsEnumerable().FirstOrDefault();

Upvotes: 0

SawtoothPunk
SawtoothPunk

Reputation: 21

From all the examples I found it seems that I need to create and register stored procedure right before I need to execute it, is it the case?

No. Stored procedure creation and registration does not have to happen during execution.

The samples are educational, but do not offer real-world patterns. See the DocumentManagement basic CRUD operations. Samples also assume a single collection too. See the Partitioning basic CRUD operations.

While I applaud the DocumentDB adaption of SQL coding conventions, the usage pattern currently falls short for .NET developers. The decade old pattern of creating CRUD stored procedures in SQL Server and then calling them by name via ADO.NET or a TableAdapter in a DataSet does not work in the DocumentDB.

Can I execute a stored procedure if all I know is a procedure name?

Yes, but it's not pretty:

StoredProcedure storedProcedure = this.DocumentClient.CreateStoredProcedureQuery(new Uri(collection.StoredProceduresLink)).Where(p => p.Id == "GetPunkRocker").AsEnumerable().FirstOrDefault();

When using a PartitionResolver, things get more complicated:

    public async Task<PunkRocker> GetPunkRockerAsync(string partitionKey)
    {
        foreach (string collectionLink in this.PartitionResolver.ResolveForRead(partitionKey))
        {
            DocumentCollection collection = this.DocumentClient.CreateDocumentCollectionQuery(new Uri(this.Database.SelfLink)).Where(c => c.SelfLink == collectionLink).AsEnumerable().FirstOrDefault();

            if (collection == null)
            {
                // Log...
                continue;
            }

            StoredProcedure storedProcedure = this.DocumentClient.CreateStoredProcedureQuery(new Uri(collection.StoredProceduresLink)).Where(p => p.Id == "GetPunkRocker").AsEnumerable().FirstOrDefault();

            if (storedProcedure == null)
            {
                // Log...
                continue;
            }

            PunkRocker punkRocker = await this.DocumentClient.ExecuteStoredProcedureAsync<PunkRocker>(new Uri(storedProcedure.SelfLink), partitionKey);

            if (punkRocker != null)
            {
                return punkRocker;
            }
        }

        return null;
    }

Upvotes: 2

Maxim
Maxim

Reputation: 174

Just tried this approach and it does not work.

client.CreateStoredProcedureQuery( link, String.Format( "select * from root r where r.id = '{0}'", "spname1" ) ).ToList(  ).FirstOrDefault( );

returns null

client.CreateStoredProcedureQuery( link, String.Format( "select * from root r" ) ).ToList( ).FirstOrDefault( );

returns correct stored procedure

{
    "id": "spname1",
    "body": "function() {  
        var context = getContext();
        var collection = context.getCollection();       
    }",
    "_rid": "XXX",
    "_ts": 1410449011.0,
    "_self": "XXX",
    "_etag": "XXX"
}

Upvotes: 0

Ryan CrawCour
Ryan CrawCour

Reputation: 2728

You can absolutely create the Stored Procedure beforehand and have it saved on the server for future use. You do not need to create it just before using it. This was for sample purposes only. We expect the majority of the time that the stored procedures will exist already and you just use them in your application.

You do need the SelfLink to execute the Stored Procedure. One way to get this would be to query for the Stored Procedure (by Name), get the SelfLink and then use that to execute the stored procedure.

Querying for a Stored Procedure by name would look something like;

StoredProcedure sproc = client.CreateStoredProcedureQuery(collection.StoredProceduresLink, "select * from root r where r.id = \"sproc name\"");

The resulting sproc variable will contain the SelfLink of the stored procedure you want to execute.

var result = client.ExecuteStoredProcedureAsync(sproc.SelfLink);

For comprehensive samples of how to work with Stored Procedures please see the DocumentDB.Samples.ServerSideScripts project in the samples posted at; http://code.msdn.microsoft.com/Azure-DocumentDB-NET-Code-6b3da8af

Upvotes: 5

Related Questions