Output Parameters for Stored Procedures

[Wardrop]

Microsoft SQL has the concept of output parameters for stored procedures. It's one of four methods for returning data from a stored procedures (http://msdn.microsoft.com/en-us/library/aa174792%28v=sql.80%29.aspx). TinyTds currently has no native support for output parameters, so this limitation must be worked around as follows:

DECLARE @out1 nvarchar(255), @out2 nvarchar(255), @result int
EXEC @result = testSproc 'some input', @out1 OUTPUT, @out2 OUTPUT
SELECT @out1 as 'out1', @out2 as 'out2', @result as 'result'

You may also notice that because the call to "EXEC" was not the last statement in this batch, using the #return_code method will return nil, so we need to add the return code (@result) to the SELECT statement in order to retrieve its value.

Ideally, it would be nice if there was a construct for stored procedures. For a few example interfaces

client = <TinyTds::Client object>
input_params = ['value1'] # Hash or array for named or unnamed parameters
output_params = {:out1 => nil, :out2 => nil} # Hash or array for named or unnamed parameters
client.sproc('testSproc', input_params, output_params).do
puts output_params[:out1] #=> "Some value"

OR...

client = <TinyTds::Client object>
# Unnamed parameters
sproc = client.sproc('testSproc').input('param1', 'param2').output(:out1, :out2)
sproc #=> <TinyTds::Sproc object>
# Or named parameters
sproc = client.sproc('testSproc').input(:param1 => 'value', :param2 => 'value').output(:out1 => nil, :out2 => nil)
sproc.execute
sproc.output[:out1] #=> Retrieves the value of the output parameter :out1.

While you can still use stored procedures with output parameters without a special interface, this would be quite a nice addition to TinyTds, and would make it into a proper MSSQL client in my opinion.

Refer to: http://www.freetds.org/faq.html#ms.output.parameters

EDIT: Whoops, my examples were completely flawed. Now fixed.

[Wardrop]

Fixed my examples which were originally fundamentally flawed. I had a brain lapse and forgot that Ruby can't pass variables by reference, only objects are passed by reference.

[metaskills]

Thanks for opening a ticket for this! Makes it easier to communicate in an open way so others can chime in if interested. Here are some of my initial thoughts and I will start work on this in some fashion soon.

I remember having some issues with OUTPUT parameters when I implemented the latest 3.1 version of the ActiveRecord adapter for SQL Server. In that version AR now passes down bind vars and allow different RDBM's to construct, tokenize how ever that DB needs to cope with prepared statements. Here is the thread if your interested.

http://groups.google.com/group/rails-sqlserver-adapter/browse_thread/thread/8a2422081d5cad6c?hl=en

I mention this because the way I implemented prepared statement support speaks to how I try to construct dispart libraries to SQL Server. My goal is to not spend too much time solving things from one library that another implements and to implement as much in TSQL vs hooking into low level connection modes specific ways of doing things. In this example, ODBC (ruby-odbc) supports prepared statement handles and reuse, where DBLIB (tiny_tds) has no such notion. The solution was just to use sp_executesql which reuses the query plan too without having to hook into special connection mode hooks. FFT.

So, I did mention that I had problems with OUTPUT params when I was working on learning the details of SQL Server's query plan reuse under TinyTDS. I think this is something that I want to solve. I worked around it in the adapters implementation of sp_executesql for getting the last identity (pk) returned by simply doing something similar to the workaround you mentioned above. By just taking on the SQL to the scope of the stored procedure's SQL since it is executed in another context.

I am on the fence about building in an explicit stored procedure hook with bind var parsing. Both the activerecord-sqlserver-adapter and more so importantly arel already do the hard work of quoting ruby primitives for arguments and/or parameterized SQL. I have an internal note to myself to consider a stronger TinyTds::Client#quote method that mimics what the adapter/arel does already. If we implement something like what your asking, that would almost certainly have to be the case.

[Wardrop]

Thanks for the reply.

[Wardrop]

Hey Ken,

Just wondering if you ever revisited the issue of output parameters. I'm also wondering whether prepared statements is on the table for TinyTDS. You say above that DBLib, which I assume TinyTds depends on, doesn't have the notion of prepared statements. Is this still the case?

Cheers, Tom

[MrLinDowsMac]

Hi, I would like to know if there is any chance to use OUTPUT variables in Rails now, I don't find any doc about it, I just found this issue from 2011... Is this not supported yet?

[metaskills]

Rails/ActiveRecord has no need for these and hence this has not moved. That said, I am interested in hearing how someone thinks TinyTDS could do this. Propose an interface, etc.

[MrLinDowsMac]

Mmm... At this point I can use execute_procedure to get results, but I have no way or don't know how to get the output variables. Sequel collaborators made something like this: https://github.com/jeremyevans/sequel/pull/748

[MrLinDowsMac]

I was planning to move to sequel-rails, but the current problem is that don't provide support for SQL Server :(

[metaskills]

From FreeTDS's FAQ site. Just posting this for my own info on when I get to this.

Microsoft SQL Server 7 with SP3, and later versions, quietly changed (which is to say, broke) how they respond to queries that execute stored procedures with output parameters. Earlier servers let you send a query like EXECUTE A @P OUTPUT and fetch the output parameter as a special result row (technique varying by library). Newer servers simply don't send back that data. To elicit output parameters from them, you have to use the RPC functions such as the DB-Library dbrpcparam.