how to get the return code value for a stored procedure?

[rails311]

I would like to execute a store procedure and get its returned value.
How can I do that?

To be specific, I use SQL Server 2008 and activerecord-sqlserver-
adapter 2.3.9. Here is what I tried, among others,
[code]
j:\myRailsApp ruby script\console
Loading development environment (Rails 2.3.9)
>> User.connection.execute_procedure("sp_getapplock", "file_1", "exclusive", "Session", 0)
=> []
[/code]

The problem is, exectue_procedure always returns [] here but I want
the return code value of that stored procedure, [url=http://
msdn.microsoft.com/en-us/library/ms189823.aspx]sp_getapplock[/url].
The return code value is critical for my program since it shows
whether the lock for file_1 is obtained or not. If it is greater or
equal to 0, the lock is acquired; otherwise, the lock is not acquired.

I have checked the source for execute_procedure. It seems it does not
check the return code value of the stored procedure. However, I might
be wrong as I am not versed in the SQLServer Adapter.

So, here is my question again: how can I execute a stored procedure
and get the its return code value?

[Ken Collins]

You are going to want to hook into the #raw_connection for this. The adapter only abstracts out the simple execution and return of data from stored procedures. The #raw_connection method of an ActiveRecord connection should give you either a ruby-odbc or tiny_tds connection. I think I remember that ruby-odbc has some features for getting the return code from a procedure. I just updated TinyTds with that capability too.

https://github.com/rails-sqlserver/tiny_tds/commit/a1740aab171649fcc921e1868618e96ad5630583

So if you are using TinyTds, then it would be possible to do something like this:

User.connection.execute_procedure "sp_getapplock", "file_1", "exclusive", "Session", 0
User.connection.raw_connection.return_code

- Ken