However, I know now that there ARE some cases where it makes sense to use a temporary stored procedure. Specifically, if these 2 conditions exist:
1. The business has very rigid procedural requirements that must be met before a new stored procedure can be put into production, and
2. You need to continually make data changes that are not possible with the regular tools and services that use the database. For instance, manually deactivating or deleting records, adding complex domain data, etc. You frequently have to make the same type of complex change over and over.
Before starting to use temporary stored procedures, I or someone on my team would write up a separate script to do whatever task needed doing. For instance, if I needed to add a set of domain data (including validation, lookups, and logging) we would script it out, save it to the source code control system, and send in a service request to get it run in production. The next time a similar script was needed we would copy and paste the old script (assuming we could find it), change the applicable values, check it in and submit another service request.
However, this type of copy and paste programming is very error prone and time-consuming (see the wikipedia entry here). It's best to avoid it where possible. What I've started doing instead is creating temporary stored procedures for these types of tasks. I script them out and save them to an "OperationalScripts" folder in our source code control tool. Then, whenever I need to do a similar task, I will create a two-step service request.
Step 1 is to create the temporary stored procedure. For instance,
Create Procedure #ConfigurationAddDomainData
(
@pProductID int
,@pComponentID int
,@pLastUpdatedBy varchar(32)
)
as
-- long script with complex error handling, validation and logging
Step 2 is a call to the temporary stored procedure.
exec #ConfigurationAddDomainData
@pProductID = 15
,@pComponentID = 28
,@pLastUpdatedBy = 'Bug 1234'
When time and resources allow, this temporary stored procedure can be incorporated into an administrative tool. But meanwhile, nobody is wasting time and effort rewriting throwaway scripts.
