Wednesday 2 March 2011

SQL Server: stored procedure with code fetched from database

   


I know this is crazy, but I've been thinking about it for a while. In the past I've talked about stored procedures with clauses taken from another table. Ok, I understand that this is difficult to explain. Let me try.
Let's say I have a table that contains all the company departments. I actually use this table to create parameters for a where-clause in a stored procedure. That is done because I need to change departments without having to change the stored procedure. When a new department is inserted into the departments table, the stored procedure works immediately, without the need to change it.

Said that, please follow me. Let's say I have 3 stored procedures that perform queries on different tables with appropriate joins. Part of the code of these 3 stored procedures is exactly the same. When I have to change that part of the code, I have to change it 3 times.

Would it be crazy if I put that part of the code in a table, fetch it, and use it in the stored procedure?

In my SQL Server database, I have more than 3 stored procedures in that situation. I understand that it is absolutely possible - in coding terms - to do what I said. What really worry me is: would it pose some performance issue?

I really would like if you share your thoughts on this. 

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.