Hi everyone,
I have a stored procedure, which i have imported by name. This is a very simple stored procedure which counts all of the rows within a table and returns the number. Is it possible to assign this value to a variable within DI?
Thank you
esinclair 
(BOB member since 2006-09-12)
Sure. Here is an example of one of mine:
DataStore.Schema.SPName(‘R’, ‘V[$N_Tabl]’, $I_PART_Glob, $Q_ROW_COUNT_Glob, $I_STATS_RETRN_Glob, $AL_SP_RETCODE_Glob, $AL_SP_ERRMSG_Glob);
The first two fields are inputs, everything else are outputs. Just add an output for your global / local and you are set.
eepjr24 
(BOB member since 2005-09-16)
Thanks for the quick reply Ernie!
I’m still having a little trouble though, when i hover over my stored proc it seems to only want two output values i.e the retcode and errmsg. When i go into my imported stored procedure there are three columns, AL_SP_RETCODE and AL_SP_ERRMSG have a green rightheaded arrow in front of them. And AL_SP_RETURN has a blue/red left headed arrow.
Any ideas?
esinclair (BOB member since 2006-09-12)
Can you look at the definition for the SP on the database? Does it have output values in the definition? Not talking about a return type from the SP, but actual output variables? Since we are on DB2 for most of our SP’s, I don’t know how much directly relates, but we cannot return rowsets or the like, we have to specify output type parameters in the definition of the SP.
eepjr24 (BOB member since 2005-09-16)
Ahhh, it is sql server 2000 we are using and no we have not specified an output value, it is just the return type!
Does this mean we cannot directly assign this to a variable without having a specific output?
Thanks again for your help!
esinclair (BOB member since 2006-09-12)
If it has a single return type, like say an integer, I would try it in a script and see. Something like:
$Q_Val_Glob = MyDS.Schema.SP($Q_Input_Parm);
Around page 595 in the DI Reference guide has some more specific examples for different DB’s.
eepjr24 (BOB member since 2005-09-16)