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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
-- before running this, create a dev utils database 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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!
|
1 2 3 4 5 6 7 8 9 10 11 |
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!
Here is an example of the sort of utility that you might want as a temporary procedure whilst developing code. You can just create it in your current query window in SSMS. From then on you can use it for creating temporary variables until you close the query
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
ALTER PROCEDURE #CreateTableVariableBuild @TheExpression NVARCHAR(4000), --the query you want stored in the Table Variable @NameOfVariable sysname = 'MyTableVariable' --(optional) the name of the variable /** Summary: > This is a temporary procedure for creating such things as table variable, temporary tables or anything else that needs a column list. It is more complicated than one might think because you can have duplicate column names in a result, but not in a table. Author: PhilFactor Date: 18/02/2019 Examples: - Declare @TheExpression NVARCHAR(MAX)=N'SELECT * FROM Sales.Customer INNER JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID INNER JOIN Person.BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID INNER JOIN Person.AddressType ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;' execute #CreateTableVariableBuild @TheExpression, 'CustomerAddresses' Returns: > The code for the DECLARE statement **/ AS BEGIN SELECT 'DECLARE @'+@NameOfVariable COLLATE DATABASE_DEFAULT+' table ('+ Stuff ((SELECT ', '+Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name) + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL' --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END AS ThePath FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 1) AS f --(@tsql, @Params, @include_browse_information -- use sys.sp_describe_first_result_set for a batch LEFT OUTER JOIN (SELECT name FROM sys.dm_exec_describe_first_result_set (@TheExpression, NULL, 0) AS f WHERE Coalesce(is_hidden,0)=0 GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames ON DetectDuplicateNames.name=f.name WHERE Coalesce(is_hidden,0)=0 ORDER BY column_ordinal FOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')' END GO |
You could then execute this as
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
DECLARE @TheExpression NVARCHAR(MAX) = N' SELECT * FROM Sales.Customer INNER JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID INNER JOIN Person.BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID INNER JOIN Person.AddressType ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID; '; EXECUTE #CreateTableVariableBuild @TheExpression, 'CustomerAddresses'; |
Yes, I know you’d be very unlikely to want to use this query: it is just an example! If you did, you would see, and then be able to use, This.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
DECLARE @CustomerAddresses table ( CustomerID int NOT NULL, PersonID int NULL, StoreID int NULL, TerritoryID int NULL, AccountNumber varchar(10) NOT NULL, rowguid_6 uniqueidentifier NOT NULL, ModifiedDate_7 datetime NOT NULL, BusinessEntityID_8 int NOT NULL, PersonType nchar(2) NOT NULL, NameStyle bit NOT NULL, Title nvarchar(8) NULL, FirstName nvarchar(50) NOT NULL, MiddleName nvarchar(50) NULL, LastName nvarchar(50) NOT NULL, Suffix nvarchar(10) NULL, EmailPromotion int NOT NULL, AdditionalContactInfo xml NULL, Demographics xml NULL, rowguid_19 uniqueidentifier NOT NULL, ModifiedDate_20 datetime NOT NULL, BusinessEntityID_21 int NOT NULL, AddressID_22 int NOT NULL, AddressTypeID_23 int NOT NULL, rowguid_24 uniqueidentifier NOT NULL, ModifiedDate_25 datetime NOT NULL, AddressID_26 int NOT NULL, AddressLine1 nvarchar(60) NOT NULL, AddressLine2 nvarchar(60) NULL, City nvarchar(30) NOT NULL, StateProvinceID int NOT NULL, PostalCode nvarchar(15) NOT NULL, SpatialLocation geography NULL, rowguid_33 uniqueidentifier NOT NULL, ModifiedDate_34 datetime NOT NULL, AddressTypeID_35 int NOT NULL, Name nvarchar(50) NOT NULL, rowguid_37 uniqueidentifier NOT NULL, ModifiedDate_38 datetime NOT NULL) |
… which you could execute successfully …
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
DECLARE @CustomerAddresses TABLE ( CustomerID INT NOT NULL, PersonID INT NULL, StoreID INT NULL, TerritoryID INT NULL, AccountNumber VARCHAR(10) NOT NULL, rowguid_6 UNIQUEIDENTIFIER NOT NULL, ModifiedDate_7 DATETIME NOT NULL, BusinessEntityID_8 INT NOT NULL, PersonType NCHAR(2) NOT NULL, NameStyle BIT NOT NULL, Title NVARCHAR(8) NULL, FirstName NVARCHAR(50) NOT NULL, MiddleName NVARCHAR(50) NULL, LastName NVARCHAR(50) NOT NULL, Suffix NVARCHAR(10) NULL, EmailPromotion INT NOT NULL, AdditionalContactInfo XML NULL, Demographics XML NULL, rowguid_19 UNIQUEIDENTIFIER NOT NULL, ModifiedDate_20 DATETIME NOT NULL, BusinessEntityID_21 INT NOT NULL, AddressID_22 INT NOT NULL, AddressTypeID_23 INT NOT NULL, rowguid_24 UNIQUEIDENTIFIER NOT NULL, ModifiedDate_25 DATETIME NOT NULL, AddressID_26 INT NOT NULL, AddressLine1 NVARCHAR(60) NOT NULL, AddressLine2 NVARCHAR(60) NULL, City NVARCHAR(30) NOT NULL, StateProvinceID INT NOT NULL, PostalCode NVARCHAR(15) NOT NULL, SpatialLocation GEOGRAPHY NULL, rowguid_33 UNIQUEIDENTIFIER NOT NULL, ModifiedDate_34 DATETIME NOT NULL, AddressTypeID_35 INT NOT NULL, Name NVARCHAR(50) NOT NULL, rowguid_37 UNIQUEIDENTIFIER NOT NULL, ModifiedDate_38 DATETIME NOT NULL ); INSERT INTO @CustomerAddresses SELECT * FROM Sales.Customer INNER JOIN Person.Person ON Customer.PersonID = Person.BusinessEntityID INNER JOIN Person.BusinessEntityAddress ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID INNER JOIN Person.Address ON BusinessEntityAddress.AddressID = Address.AddressID INNER JOIN Person.AddressType ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID; |
Load comments