How do I get return parameters from a stored procedure call?

[Mark_Ireton]

The Open SQL Statement has an option on the Advanced tab to specify a command type of 'stored procedure'. In addition to returning a recordset, a stored procedure can also return parameters (see http://support.microsoft.com/support/kb/articles/Q185/1/25.ASP for info on doing this with ADO). Is it possible to get those return parameters with TestStand? In particular, I want to be able to get error codes back from the stored procedure in case it fails (maybe there is another way).

[Ryan Ramcharan]

Yes. For each column defined in the Columns tab, TestStand will create a parameter. For output parameters, TestStand will retrieve the parameter value AFTER the statement has executed. Stored procedures can also a return a value in addition to output paramters. Set the Type under the Statements tab to Stored Procedures.

[Scott_Richardson]

The Open SQL Statement step type does not fully support stored procedures. If the procedure returns a record set than you can fetch the values as you would a SELECT statement. Stored procedures require you to setup the parameters before the call and this is not yet supported. Bob, in his answer, made a reference to the Statements tab and I think that he was talking about the Database Logging feature in TS 2.0.

If the stored procedure is returning a return value, it may return as a single column, single row recordset which can be fetched as you normally do a record set.

[Mark_Ireton]

Any idea when there will be support for output parameters? Stored procedures are much more useful when one can use the output parameters.

[Scott_Richardson]

Mark -
It is on our suggestion list, but it is not targeted for any specific release.

Scott Richardson - NI

[bmoyer]

Does anyone know if this item in the suggestion list was implemented since 2002?

[J_Stape]

Hi bmoyer,

Yes, this feature has been implemented for TestStand versions 3.0 and newer.