Calling a SQL Server stored procedure via ODBC

Hi,

I'm trying to ultimately create a function that I can use with HL7 messages that calls a stored procedure held on a SQL Server.

Initially I tried extending the function class to use the sql inbound adapter and/or EnsLib.SQL.Common, but this wouldn't work from the class method for the function.

Then I attempted to use the linked stored procedure using the wizard where it connected fine and I could find the sp but but it wouldn't return any data when ran. The test sp should just return an int.

The class document produced is this.

I suppose my question is, is it even possible to call a sp on a SQL Server to use in a function? If so, then any pointers or examples would be really appreciated.

Thanks.