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.
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.