Stored Procedures, One Database, Different Software Releases

5 pts.
Tags:
Release management
Software maintenance
SQL
Stored Procedures
VB.NET
Hi Everyone. Currently at my work we use SQL in our VB.NET code. I have previously always used stored procedures for all of their benefits. My company has said that our customers might use different versions of our software against its one database. For example: While a customer is migrating from release 1 to release 2, they will need the release 1 & 2 stored procedures. So calling the sp the same name i.e. spGetCustomerDetails is not going to work if the sql behind it has changed from release 1 to 2. Without renaming the sp's with each version, thus creating spGetCustomerDetails_v1 & spGetCustomerDetails_v2 etc is there a more efficent way to use stored procedures and move away from SQL in code. Hope that all makes sense & thanks in advance peeps.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Usually, I will add an optional parameter and use that as an indicator. Here is an example.

Let’s say you are adding a field in the Customers table called TwitterId and your latest app version is using this field. Let’s also say you have a Customers_Insert stored procedure that is used to store new records into the db. Your current SP would look like this:

Create Procedure dbo.Customer_Insert (
@CustomerName varchar(50))
As
Insert Into Customers(CustomerName)
Values(@CustomerName)

Now, you can change the stored procedure to this:

Alter Procedure dbo.Customer_Insert(
@CustomerName varchar(50),
@TwitterId varchar(50) = ‘<not passed>’)
As
Insert Into Customers(CustomerName, TwitterId)
Values (@CustomerName, @TwitterId)

The new version of your app will pass a @TwitterId parameter; your old one will not, thus the value will be ‘<not passed>’.

Hope this helps.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following