Tuesday, October 26, 2010

Add new parameter to Stored Procedure without impacting existing call

I have a Stored procedure already deployed in a production box, there is another porcess which wants to use it in a batch process, but the main batch process wants to control the transaction (commit/rollback). What can I do??
Add another parameter to my SP to handle the transaction or not, but If I do this the signature (DDL) of my SP is going to change!
There is one way to do this, something like adding polymorphism, on this case overload SP.
This is my original SP: On my iBatis side I'm calling this SP with this number of Params (5), If I add a new param, I'll have to add a new one to my DDL, right?
Not necessarily, because If you add another parameter but you don't declare it as IN neither OUT, it will be optional!!!
Then I can do this: And nobody will notice that I change it, because you can call it with 5 params (The 6th param will be given by default in the signature: DEFAULT 1) or with 6 (assigning 0 for example to the 6th parameter)
Inside the body of my SP I'm doing something like this:

No comments:

Post a Comment