• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 22
  • Last Modified:

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
0
rajathi_franco
Asked:
rajathi_franco
  • 2
3 Solutions
 
HuaMinChenBusiness 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
 
Snarf0001Commented:
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
 
Snarf0001Commented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now