There is no documentation in Rails about how to use stored procedures or atleast it is difficult to find where to look at. And according to DHH stored procedures is not the Rails Way to do things. DHH mentioned in his post
I want a single layer of cleverness: My domain model. Object-orientation is all about encapsulating clever. Letting it sieve half ways through to the database is a terrible violation of those fine intentions. And I want no part of it.
I somewhat agree to what DHH has to say. And that’s all fine but what if we don’t have any other option apart from using stored procedure and our stored procedure has some complex logic which we don’t want to put in our Ruby code then here is a little hack to it
But the above code will not return anything it will only execute your procedure, considering everything went OK.
Moreover, it’s not DRY if everytime you have to write that line of code just to call a procedure.
Here is a better way to do it:
1) Create a class and let’s call it
Class DbStoredProcedure your methods here ....... end
2) Create a class method within it
def self.fetch_db_records(proc_name_with_parameters) ActiveRecord::Base.connection.execute(proc_name_with_parameters) end
Then simply call it as
DbStoredProcedure.fetch_db_records("exec procedure_name 'args1', 'args2', 'and so on' ")
As mentioned above, this will only execute a procedure without returning any value. To return records or values from stored procedures can use other methods provided by the AR,
class DbStoredProcedure < ActiveRecord::Base def self.fetch_db_records(proc_name_with_parameters) connection.select_all(proc_name_with_parameters) end def self.insert_update_delete_calculate(proc_name_with_parameters) connection.execute(proc_name_with_parameters) end def self.fetch_val_from_sp(proc_name_with_parameters) connection.select_value(proc_name_with_parameters) end end
And then simply call it like
DbStoredProcedure.method_name("exec procedure_name 'args1', 'args2', 'and so on' ")
If you know a better way to do it then I would love to know your views.