I’ve often read in forums how people have special utility databases with all their stored procedures and functions for working on the databases on the server. It is great because you don’t want your utilities intruding into the actual databases that you are developing or testing.
The problem is that it doesn’t work. Let me demonstrate.
We’ll pretend that we have a database called ‘MyDevStuff’ with all our lovely tools. You’ll need to create this. Then we put in a dummy utility. In reality, it will be cunning routines for doing reports, checking for code smells, outputting the contents of tables, making metadata queries, creating documentation and so on.
Instead, we will create a single procedure that does nothing more than to report its database context, and we’ll run it on every database in the instance. At the same time, we’ll create an identical temporary procedure.
-- before running this, create a dev utils directory called MyDevStuff USE MyDevStuff; GO --now create a pretend utility that actually just reports its database context CREATE OR ALTER PROCEDURE WhatDatabaseAmIin @CurrentDatabase sysname OUTPUT AS SELECT @CurrentDatabase = Db_Name(); GO --next create an identical temporary procedure CREATE OR ALTER PROCEDURE #WhatDatabaseAmIin @CurrentDatabase sysname OUTPUT AS SELECT @CurrentDatabase = Db_Name(); GO /* we will now test out what we've done on the current directory */ DECLARE @MyCurrentDatabase sysname; DECLARE @MyCurrentDatabaseInTempVersion sysname; EXECUTE MyDevStuff.dbo.WhatDatabaseAmIin @MyCurrentDatabase OUTPUT; EXECUTE #WhatDatabaseAmIin @MyCurrentDatabaseInTempVersion OUTPUT; SELECT @MyCurrentDatabase AS where_the_Procedure_Is, @MyCurrentDatabaseInTempVersion AS where_the_temp_Procedure_Is, Db_Name() AS where_I_really_am; go /* Now we will prepare to test this out in every database. Firstly we create a string with the SQL to execute that we just tested */ */ DECLARE @command NVARCHAR(4000) = ' use ? DECLARE @MyCurrentDatabase Sysname DECLARE @MyCurrentDatabaseInTempVersion Sysname EXECUTE MyDevStuff.dbo.WhatDatabaseAmIin @MyCurrentDatabase OUTPUT EXECUTE #WhatDatabaseAmIin @MyCurrentDatabaseInTempVersion OUTPUT SELECT @MyCurrentDatabase AS where_the_Procedure_Is,@MyCurrentDatabaseInTempVersion AS where_the_temp_Procedure_Is,Db_Name() as where_I_really_am '; /* we want to put our results in just one table */ DECLARE @MyResults TABLE ( where_the_Procedure_is sysname, where_the_temp_Procedure_is sysname, where_l_really_am sysname ); /* now we insert into our table variable the results of executing the test SQL in all databases. We don't really need to but it proves the point */ INSERT INTO @MyResults (where_the_Procedure_is, where_the_temp_Procedure_is, where_l_really_am) EXECUTE sp_MSforeachdb @command; SELECT where_the_Procedure_is, where_the_temp_Procedure_is, where_l_really_am FROM @MyResults;
The result is
where_l_really_am where_the_Procedure_is where_the_temp_Procedure_is --------------------------- ----------------------- ----------------------------- master MyDevStuff master tempdb MyDevStuff tempdb model MyDevStuff model msdb MyDevStuff msdb RedGateMonitor MyDevStuff RedGateMonitor AdventureWorks2016 MyDevStuff AdventureWorks2016 WideWorldImporters MyDevStuff WideWorldImporters PhilFactor MyDevStuff PhilFactor Antipas MyDevStuff Antipas Phasael MyDevStuff Phasael Archaelus MyDevStuff Archaelus Northwind MyDevStuff Northwind Shadrach MyDevStuff Shadrach Abednego MyDevStuff Abednego Meshach MyDevStuff Meshach Daniel MyDevStuff Daniel pubs MyDevStuff pubs MyDevStuff MyDevStuff MyDevStuff
Well, to look on the positive side, the procedure was correct when it was in its own database. but if a procedure is executed, it uses its own context. However, a temporary procedure uses whatever context it is executed in. This can be very useful. I recently saw a comment on StackOverflow from a user who said that temporary Procedures had no apparent use. I don’t agree.
This means that you can only use temporary procedures or registered routines. I must explain that it is perfectly possible to create system procedures , functions and views. The problem with these is in deploying them. They are also intrusive into the master database, and you really ought to leave the master database alone. A lot of us use them but it is definitely a code smell.
Can one create a temporary procedure within a procedure? Then you can call an initialization routine and avoid having to put all the actual code of your routine in a batch every time you want to use it.
USE MyDevStuff;
GO
--now create a pretend utility that creates a tewmporary procedure
CREATE OR ALTER PROCEDURE InitStuff
AS
IF Object_Id('tempdb..#ThisIsStrangeAndAlarming') IS NOT NULL
DROP PROCEDURE #ThisIsStrangeAndAlarming;
EXECUTE sp_executesql N'
CREATE procedure #ThisIsStrangeAndAlarming
@CurrentDatabase sysname OUTPUT
AS
SELECT @CurrentDatabase = Db_Name();
';
GO
EXECUTE MyDevStuff.dbo.InitStuff;
USE MyDevStuff;
DECLARE @MyCurrentDatabaseInTempVersion sysname;
EXECUTE #ThisIsStrangeAndAlarming @MyCurrentDatabaseInTempVersion OUTPUT;
SELECT @MyCurrentDatabaseInTempVersion;
USE AdventureWorks2016;
EXECUTE #ThisIsStrangeAndAlarming @MyCurrentDatabaseInTempVersion OUTPUT;
SELECT @MyCurrentDatabaseInTempVersion;
Yes. Contrary to all expectations, a temporary procedure is treated differently to a temporary table. It is not disposed of at the end of the procedure. The temporary table must be cleared away at the end of a procedure and you can see it happening here.
CREATE OR ALTER PROCEDURE CreateATempTable
AS
IF Object_Id('tempdb..#ThisisMyTempTable') IS NOT NULL DROP TABLE #ThisisMyTempTable;
EXECUTE sp_executesql N'
CREATE table #ThisisMyTempTable
(MyID int identity primary key, MyJSON NVarchar(max))
';
GO
SELECT * FROM #ThisisMyTempTable;
/*
Msg 208, Level 16, State 0, Line 87
Invalid object name '#ThisisMyTempTable'.
*/
Because temporary procedures last for the entire session or connection, it means that you can use your initialization procedure to create the utilities you create at the start of your connection and they will continue to exist for the life of the connection. That procedure ‘InitStuff’ is permanent in your utilities database, and by calling it you either get your temporary procedures updated or created.
As always, there is a snag. You can’t create temporary functions or views. It is just procedures.
Aha! (I hear you say), all you need to do is to employ the USE command at the start of the function and specify the database context that you want the body of the routine to execute in.
Just try it!
USE MyDevStuff; GO --now create a pretend utility that actually just reports its database context CREATE OR ALTER PROCEDURE BetterWhatDatabaseAmIin @context sysname=DB_NAME(),@CurrentDatabase sysname OUTPUT AS USE @context SELECT @CurrentDatabase = Db_Name(); GO /* Msg 154, Level 15, State 1, Procedure BetterWhatDatabaseAmIin, Line 3 [Batch Start Line 94] a USE database statement is not allowed in a procedure, function or trigger. */
I’ve found myself using temporary procedures quite a lot for tasks that require scripting across databases. I do it because it is less intrusive and requires less tear-down. Temporary procedures vanish without trace when the connection or session is terminated. In code, It is possible to create them within a session, use them and then close the connection. It would be possible to use prepared statements but these would have to be referenced by a handle in a variable, and finally the handle would have to be disposed of: That is an unpleasant complication.
There is an irritating problem with any temporary routine: It means updating the scripts, applications and files whenever you change or improve the procedure. It isn’t a good idea to have more than one source. If you maintain these from an initialization procedure held anywhere on the instance, then this problem is contained: You just alter the initialization procedure in your utility database from a single source in source-control.
A slick way around all this is to create pretend system views, functions or procedures. This is too intrusive and is seldom allowed. Imagine wanting to do it on a production server!
The post Using Temporary Procedures appeared first on Simple Talk.
from Simple Talk http://bit.ly/2UQNgwB
via
No comments:
Post a Comment