User Stored Procedure as system stored procedure

Hi,

Our application has one database for each customer and we have several 100 customers. Each database has same set of stored procedures. we are planning to convert these stored procedures as system stored procedures to have ease on maintenance. Please let us know the downside of it if any

Thanks,
Ganapathi
rajathi_francoAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HuaMinChenConnect With a Mentor Business AnalystCommented:
You can instead have only one customer table for dealing with all customers. Then within only one schema there, you can easily handle with whatever new logic specifically for each customer.
0
 
Snarf0001Connect With a Mentor Commented:
System stored procedures are great, I've used them on a couple projects before with similar requirements.  In this case it was due to stringent security policies, enforcing that each "client" had completely segregated data.

The only downside I've found, is you lose the ability to incrementally upgrade the systems.
If you had two customers moving to version 2.0 for example (assuming there were database changes involved), you would either have to duplicate any conflicting procs with a 2.0 or something, or upgrade everyone at once.
0
 
Snarf0001Connect With a Mentor Commented:
Depending on how heavy the use is, there are marginal performance issues to consider as well.

This article does a good job explaining it in detail:

http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.