Software Development in North Wales

I'm still evaluating Ruby on Rails as a plaform for future web apps. While I like the idea of the ActiveRecord and auto-generation of web forms from database tables, I agree totally with Jacec's post on ZDNet about the benefits of stored procedures:

In MS SQL parlance, a stored procedure is something that resides on the server, NOT in an application's code. These server-side stored procedure generate an execution plan ONCE and store it, leading to future performance gains and efficiency, freeing the CPU for other tasks, etc.

The benefits of using stored procedures in SQL Server rather than Transact-SQL programs stored locally on client computers are:

They allow modular programming.
You can create the procedure once, store it in the database, and call it any number of times in your program. Stored procedures can be created by a person who specializes in database programming, and they can be modified independently of the program source code.

They allow faster execution.
If the operation requires a large amount of Transact-SQL code or is performed repetitively, stored procedures can be faster than batches of Transact-SQL code. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

They can reduce network traffic.
An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network.

They can be used as a security mechanism.
Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly.

So, that said, how to call a stored procedure from Rails? Rahoul Baruah's Made of Stone blog has the answer (and solves a gotcha):

I know this is not "the Rails way", but I've got an urgent deadline and the stuff that the stored procedure does is 1) well tested and 2) very complex.

So I need to call it, to get this stuff done in time.

Afterwards I will wrap a unit test around it (that's a story for another time) so I can reimplement it in pure Rails, but for that to happen as it should, I also need to get the webservice stuff working (so the logic currently tied up in the stored procedure is accessible to the outside world) and rewrite parts of the desktop application to call the webservice instead of the proc. So a bit of a way off yet.

Anyway, to call a stored procedure in Rails I used the following:

sql = 'declare @result int \n'
sql += 'declare @some_value uniqueidentifier \n'
sql += 'exec @result = my_stored_procedure @input1 = 'hello', @input2 = 'world', @output = @some_value output \n'
sql += 'select @result\n' # change this to select @some_value if you want to access the output parameter
result = connection.select_value(sql)

You could probably change the last line of the SQL to select @result, @some_value and use select_all to grab the results if you need to.

Anyway, this is the equivalent of what I would do in Query Analyser - declare some variables for storing stuff, call the proc (using @result to store the return value) and then select the values I am interested in to take a look at them.

However, sometimes it worked and sometimes I would get a 'cannot perform this object when the dataset is closed' - looking at the stack trace, it appeared to be coming from the ADO Recordset itself.

Why? There seemed to be no reason for it, until my colleague Nick had a revelation. ADO allows a single Recordset object to contain multiple Recordsets (excellent class naming there Bill). When you send a compound statement via Query Analyser, it splits the actual recordsetsinto the "data pane" and various messages into the "messages pane". However, in ADO the messages sometimes get sent back as a recordset. Therefore, Rails was trying to read a "recordset" containing "2 row(s) affected" when the actual data it was interested was in the "next" recordset. As I wasn't sure if I could get at the underlying Recordset, and I didn't really want to, I was a bit stumped. But Nick came to the rescue again - adding the line "set nocount on" as the very first statement, before the declares, seemed to solve the problem. Basically, we were telling SQL Server to suppress the messages and it seems to work.

Of course, there will be some configuration somewhere, when it goes live, that will screw it up - so I need to start copying that proc into Rails as soon as.

In a later post, Rahoul provides a link to an opensource Ruby module that wraps this functionality up.

There's more about calling stored procs on the Rails Wiki.