Is it possible to use stored procedure OUTPUT parameters from a C# server side script?

There is a way to work with the return value of a stored procedure, but I'm not sure if there is a way to work with output parameters using the "ExecuteProcedure" method.  Here's how you can work with the return value.

First you will need to prepare your stored procedure correctly by placing a "SET NOCOUNT ON" statement near the top of the procedure.  This will ensure that the only return value your code will receive is the actual return value.  Here's an example of a simple stored procedure I will use for this example:

CREATE PROC dbo.usp_NewTestBasicTableRecord2(
     @RowDesc nvarchar(35),
     @RelatedID int
)
AS

     SET NOCOUNT ON; 

     INSERT INTO dbo.TestBasicTable (RowDesc, RelatedID)
     VALUES (@RowDesc, @RelatedID)

     SELECT SCOPE_IDENTITY()

     SET NOCOUNT OFF;

The table has an identity column (integer) which is what I want my stored procedure to return, and this is handled in the "SELECT SCOPE_IDENTITY()" statement.

Now let's say I have a Command Button control on a form, and when I click it I want it to execute my stored procedure with some parameters and return the newly created identity value and store it in a local custom variable.  First, add a new button to the form.  Then click on the button's "When button pressed" property.  Next, in the Visual Script Activity window drag a "Code Activity" object and add it to the script.  Double-click on the "Code Activity" object to open the editor.

Here's the sample code I want to use to execute my stored procedure:

Metastorm.Runtime.Core.QueryParameter[] oParams = new Metastorm.Runtime.Core.QueryParameter[2];
oParams[0] = Mstm.SQLArg("@RowDesc", "New Row Description");
oParams[1] = Mstm.SQLArg("@RelatedID", 65); 

Metastorm.Runtime.Contracts.IDataContainer oResults = Mstm.SelectSql(null, "EXEC dbo.usp_NewTestBasicTableRecord2 @RowDesc, @RelatedID", oParams);

if (oResults.Integer != null) 
     Local.intNewSYSID = oResults.Integer;

Let's go through this code.  The first line creates a QueryParameters array object called "oParams" with a dimension of 2.  Then I populate the 2 elements of the array by creating calling the "SQLArg" method passing a for the argument name (hint: this should be the name of the INPUT parameter for your stored procedure) followed by an object containing the value I want to pass into the parameter.  In this case I've hardcoded a string for the first parameter and an integer for the second parameter.  You most likely would use custom variables here in the real world and not hard coded values.

The next line is the key to make this all work.  First we declare an instance of an "IDataContainer" object and name it "oResults", and we assign it to the return value of the "Mstm.SelectSql" method.  This method requires you pass 3 arguments: the database connection, the sql command to be executed, and an array of QueryParameters.  I passed "null" for the database connection so that it would use the "MetastormDefault" connection.  The sql command shown above shows you that you need to format your string with the "EXEC" statement and the names of the INPUT parameters you will be supplying in the QueryParameters array.

When the SelectSQL method executes it returns an IDataContainer object which contains the results of the Sql command (if any).  So the first thing I do is check if the oResults equals null.  If it does contain a value I can use that value in my code, like I did where I assigned the results to an integer custom variable.  Notice I used the "oResults.Integer" property to return a properly formatted value for my custom variable so that I did not need to perform any type casting.  The other properties you can use to return values from an IDataContainer object as a specific type that relate to MBPM custom variables are:

Check

Currency

DateTime

Integer

List

Memo

Real

Text

So even though this doesn't answer your question on how to interact with OUTPUT parameters themselves it does show one method how you can work with the return value of a stored procedure.

Thanks Scott.  I actually asked the helpdesk for this syntax, as I couldn't find it anywhere.  Is there anywhere that outlines the syntax for all of your Mstm object functions?

Thanks Scott, my designer user guide is from May 2010.  Is there a more recent guide with an exhaustive reference to the Mstm objects.  I only have a page and a half related to SQL Mode with basic information that I've already used in the designer.  I'm looking for a more code driven reference.

The function I was trying to use initially was Mstm.ExecuteProcedure to run my stored proc and could not get the syntax. 

Any information you can provide will be useful.  Please let me know if my documentation is old.

Thanks!

Hi Scott - so how would you code a database connection "MyConnection" as the first parameter of the sqlselect ?

I can see it needs a parameter of type "ConnectInfo" - but how do I define & set that to "MyConnection" in my code

Thanks

Barrie