How do I execute an existing stored procedure in DocumentDB?
I want to create a stored procedure in DocumentDB and use it when I need it later. In order to execute the stored procedure, I need to know the StoredProcedureLink. When I register a stored procedure with the CreateStoredProcedureAsync method, I get a reference, should I save this reference somewhere myself if I want to execute this stored procedure later? Can I execute a stored procedure if all I know is the procedure name? From all the examples I've found, it seems like I need to create and register a stored procedure right before I need to execute it, doesn't it?
source to share
Just tried this approach and it doesn't 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 the correct stored procedure
{
"id": "spname1",
"body": "function() {
var context = getContext();
var collection = context.getCollection();
}",
"_rid": "XXX",
"_ts": 1410449011.0,
"_self": "XXX",
"_etag": "XXX"
}
source to share
You can absolutely create a stored procedure ahead of time and save it on the server for later use. You don't need to create it just before using it. This was for trial purposes only. We expect that in most cases the stored procedures will already exist and you just use them in your application.
You need SelfLink to execute the stored procedure. One way to get this is to query the stored procedure (by name), get the SelfLink, and then use it to execute the stored procedure.
A query for a stored procedure by name would look something like this:
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 that you want to execute.
var result = client.ExecuteStoredProcedureAsync(sproc.SelfLink);
For comprehensive examples of working with stored procedures, see the DocumentDB.Samples.ServerSideScripts project in the samples posted at; http://code.msdn.microsoft.com/Azure-DocumentDB-NET-Code-6b3da8af
source to share
From all the examples I've found, it seems like I need to create and register a stored procedure right before I need to execute it, doesn't it?
Not. The creation and registration of a stored procedure should not be done at runtime.
Samples are educational, but do not offer actual models. See Basic CRUD DocumentManagement Operations . Samples also accept one collection. See Separating Basic CRUD Operations .
While I applaud DocumentDB's adaptation of the SQL coding conventions, the usage pattern is currently not suitable for .NET developers. The decimal pattern of creating CRUD stored procedures in SQL Server and then calling them by name through ADO.NET or the TableAdapter on the DataSet does not work in DocumentDB.
Is it possible to execute a stored procedure if all I know is the name of the procedure?
Yes, but it's not pretty:
StoredProcedure storedProcedure = this.DocumentClient.CreateStoredProcedureQuery(new Uri(collection.StoredProceduresLink)).Where(p => p.Id == "GetPunkRocker").AsEnumerable().FirstOrDefault();
When using 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;
}
source to share