I\u2019ve 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\u2019t want your utilities intruding into the actual databases that you are developing or testing.<\/p>\n
The problem is that it doesn\u2019t work. Let me demonstrate.<\/p>\n
We\u2019ll pretend that we have a database called \u2018MyDevStuff\u2019 with all our lovely tools. You\u2019ll 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.<\/p>\n
Instead, we will create a single procedure that does nothing more than to report its database context, and we\u2019ll run it on every database in the instance. At the same time, we\u2019ll create an identical temporary procedure.<\/p>\n
-- before running this, create a dev utils database called MyDevStuff\r\nUSE MyDevStuff;\r\nGO --now create a pretend utility that actually just reports its database context\r\nCREATE OR ALTER PROCEDURE WhatDatabaseAmIin @CurrentDatabase sysname OUTPUT\r\nAS\r\nSELECT @CurrentDatabase = Db_Name();\r\nGO\r\n --next create an identical temporary procedure\r\nCREATE OR ALTER PROCEDURE #WhatDatabaseAmIin @CurrentDatabase sysname OUTPUT\r\nAS\r\nSELECT @CurrentDatabase = Db_Name();\r\nGO\r\n\/* we will now test out what we've done on the current directory *\/\r\nDECLARE @MyCurrentDatabase sysname;\r\nDECLARE @MyCurrentDatabaseInTempVersion sysname;\r\nEXECUTE MyDevStuff.dbo.WhatDatabaseAmIin @MyCurrentDatabase OUTPUT;\r\nEXECUTE #WhatDatabaseAmIin @MyCurrentDatabaseInTempVersion OUTPUT;\r\nSELECT @MyCurrentDatabase AS where_the_Procedure_Is,\r\n @MyCurrentDatabaseInTempVersion AS where_the_temp_Procedure_Is,\r\n Db_Name() AS where_I_really_am;\r\ngo\r\n\/*\r\nNow we will prepare to test this out in every database. Firstly\r\nwe create a string with the SQL to execute that we just tested *\/\r\n*\/ \r\nDECLARE @command NVARCHAR(4000) =\r\n '\r\nuse ? \r\nDECLARE @MyCurrentDatabase Sysname\r\nDECLARE @MyCurrentDatabaseInTempVersion Sysname\r\nEXECUTE MyDevStuff.dbo.WhatDatabaseAmIin @MyCurrentDatabase OUTPUT\r\nEXECUTE #WhatDatabaseAmIin @MyCurrentDatabaseInTempVersion OUTPUT\r\nSELECT @MyCurrentDatabase AS where_the_Procedure_Is,@MyCurrentDatabaseInTempVersion AS where_the_temp_Procedure_Is,Db_Name() as where_I_really_am\r\n';\r\n\/* we want to put our results in just one table *\/\r\nDECLARE @MyResults TABLE\r\n (\r\n where_the_Procedure_is sysname,\r\n where_the_temp_Procedure_is sysname,\r\n where_l_really_am sysname\r\n );\r\n\/* now we insert into our table variable the results of executing the test SQL\r\nin all databases. We don't really need to but it proves the point *\/ \r\nINSERT INTO @MyResults (where_the_Procedure_is, where_the_temp_Procedure_is,\r\nwhere_l_really_am)\r\nEXECUTE sp_MSforeachdb @command;\r\nSELECT where_the_Procedure_is, where_the_temp_Procedure_is, where_l_really_am\r\n FROM @MyResults;\r\n<\/pre>\nThe result is<\/p>\n
where_l_really_am where_the_Procedure_is where_the_temp_Procedure_is\r\n--------------------------- ----------------------- -----------------------------\r\nmaster MyDevStuff master\r\ntempdb MyDevStuff tempdb\r\nmodel MyDevStuff model\r\nmsdb MyDevStuff msdb\r\nRedGateMonitor MyDevStuff RedGateMonitor\r\nAdventureWorks2016 MyDevStuff AdventureWorks2016\r\nWideWorldImporters MyDevStuff WideWorldImporters\r\nPhilFactor MyDevStuff PhilFactor\r\nAntipas MyDevStuff Antipas\r\nPhasael MyDevStuff Phasael\r\nArchaelus MyDevStuff Archaelus\r\nNorthwind MyDevStuff Northwind\r\nShadrach MyDevStuff Shadrach\r\nAbednego MyDevStuff Abednego\r\nMeshach MyDevStuff Meshach\r\nDaniel MyDevStuff Daniel\r\npubs MyDevStuff pubs\r\nMyDevStuff MyDevStuff MyDevStuff\r\n<\/pre>\nWell, 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\u2019t agree.<\/p>\n
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.<\/p>\n
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.<\/p>\n
USE MyDevStuff;\r\nGO\r\n--now create a pretend utility that creates a tewmporary procedure\r\nCREATE OR ALTER PROCEDURE InitStuff\r\nAS\r\nIF Object_Id('tempdb..#ThisIsStrangeAndAlarming') IS NOT NULL\r\n DROP PROCEDURE #ThisIsStrangeAndAlarming;\r\nEXECUTE sp_executesql N'\r\nCREATE procedure #ThisIsStrangeAndAlarming\r\n @CurrentDatabase sysname OUTPUT\r\nAS\r\nSELECT @CurrentDatabase = Db_Name();\r\n';\r\nGO\r\n\r\nEXECUTE MyDevStuff.dbo.InitStuff;\r\nUSE MyDevStuff;\r\nDECLARE @MyCurrentDatabaseInTempVersion sysname;\r\nEXECUTE #ThisIsStrangeAndAlarming @MyCurrentDatabaseInTempVersion OUTPUT;\r\nSELECT @MyCurrentDatabaseInTempVersion;\r\nUSE AdventureWorks2016;\r\nEXECUTE #ThisIsStrangeAndAlarming @MyCurrentDatabaseInTempVersion OUTPUT;\r\nSELECT @MyCurrentDatabaseInTempVersion;\r\n<\/pre>\nYes. 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.<\/p>\n
CREATE OR ALTER PROCEDURE CreateATempTable\r\nAS\r\nIF Object_Id('tempdb..#ThisisMyTempTable') IS NOT NULL DROP TABLE #ThisisMyTempTable;\r\nEXECUTE sp_executesql N'\r\nCREATE table #ThisisMyTempTable\r\n \r\n (MyID int identity primary key, MyJSON NVarchar(max))\r\n';\r\nGO\r\nSELECT * FROM #ThisisMyTempTable;\r\n\/*\r\nMsg 208, Level 16, State 0, Line 87\r\nInvalid object name '#ThisisMyTempTable'.\r\n*\/\r\n<\/pre>\nBecause 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 \u2018InitStuff\u2019<\/strong> is permanent in your utilities database, and by calling it you either get your temporary procedures updated or created.<\/p>\n
As always, there is a snag. You can\u2019t create temporary functions or views. It is just procedures.<\/p>\n
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.<\/p>\n
Just try it!<\/p>\n
USE MyDevStuff;\r\nGO --now create a pretend utility that actually just reports its database context\r\nCREATE OR ALTER PROCEDURE BetterWhatDatabaseAmIin @context sysname=DB_NAME(),@CurrentDatabase sysname OUTPUT\r\nAS\r\nUSE @context\r\nSELECT @CurrentDatabase = Db_Name();\r\nGO\r\n\/*\r\nMsg 154, Level 15, State 1, Procedure BetterWhatDatabaseAmIin, Line 3 [Batch Start Line 94]\r\na USE database statement is not allowed in a procedure, function or trigger.\r\n*\/\r\n<\/pre>\nI\u2019ve 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.<\/p>\n
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\u2019t 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.<\/p>\n
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!<\/p>\n
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\u00a0\u00a0<\/p>\n
ALTER PROCEDURE #CreateTableVariableBuild @TheExpression NVARCHAR(4000), --the query you want stored in the Table Variable\r\n @NameOfVariable sysname = 'MyTableVariable' --(optional) the name of the variable\r\n\/**\r\nSummary: >\r\nThis is a temporary procedure for creating such things as table variable,\r\n temporary tables or anything else that needs a column list. It is more\r\n complicated than one might think because you can have duplicate column \r\n names in a result, but not in a table. \r\nAuthor: PhilFactor\r\nDate: 18\/02\/2019\r\nExamples:\r\n - Declare @TheExpression NVARCHAR(MAX)=N'SELECT *\r\n FROM Sales.Customer\r\n INNER JOIN Person.Person \r\n ON Customer.PersonID = Person.BusinessEntityID\r\n INNER JOIN Person.BusinessEntityAddress\r\n ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID\r\n INNER JOIN Person.Address\r\n ON BusinessEntityAddress.AddressID = Address.AddressID\r\n INNER JOIN Person.AddressType\r\n ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;'\r\n\t execute #CreateTableVariableBuild @TheExpression, 'CustomerAddresses'\r\nReturns: >\r\n The code for the DECLARE statement\r\n**\/\r\nAS\r\n BEGIN\r\nSELECT 'DECLARE @'+@NameOfVariable COLLATE DATABASE_DEFAULT+' table ('+\r\n Stuff ((SELECT ',\r\n '+Coalesce(DetectDuplicateNames.name+'_'+Convert(VARCHAR(5),f.column_ordinal),f.name)\r\n + ' '+ System_type_name + CASE WHEN is_nullable = 0 THEN ' NOT' ELSE ''END+' NULL'\r\n --+ CASE WHEN collation_name IS NULL THEN '' ELSE ' COLLATE '+collation_name END\r\n AS ThePath\r\n FROM sys.dm_exec_describe_first_result_set\r\n (@TheExpression, NULL, 1) AS f \r\n --(@tsql, @Params, @include_browse_information\r\n -- use sys.sp_describe_first_result_set for a batch\r\n LEFT OUTER JOIN \r\n (SELECT name FROM sys.dm_exec_describe_first_result_set\r\n (@TheExpression, NULL, 0) AS f \r\n\t WHERE Coalesce(is_hidden,0)=0\r\n GROUP BY name HAVING Count(*)>1) AS DetectDuplicateNames\r\n ON DetectDuplicateNames.name=f.name\r\n WHERE Coalesce(is_hidden,0)=0\r\nORDER BY column_ordinal\r\nFOR XML PATH (''), TYPE).value('.', 'varchar(max)'),1,1,'')+')' \r\nEND\r\nGO<\/pre>\nYou could then execute this as<\/p>\n
DECLARE @TheExpression NVARCHAR(MAX) = N'\r\nSELECT *\r\n FROM Sales.Customer\r\n INNER JOIN Person.Person\r\n ON Customer.PersonID = Person.BusinessEntityID\r\n INNER JOIN Person.BusinessEntityAddress\r\n ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID\r\n INNER JOIN Person.Address\r\n ON BusinessEntityAddress.AddressID = Address.AddressID\r\n INNER JOIN Person.AddressType\r\n ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;\r\n';\r\nEXECUTE #CreateTableVariableBuild @TheExpression, 'CustomerAddresses';\r\n<\/pre>\nYes, 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.<\/p>\n
DECLARE @CustomerAddresses table (\r\n CustomerID int NOT NULL,\r\n PersonID int NULL,\r\n StoreID int NULL,\r\n TerritoryID int NULL,\r\n AccountNumber varchar(10) NOT NULL,\r\n rowguid_6 uniqueidentifier NOT NULL,\r\n ModifiedDate_7 datetime NOT NULL,\r\n BusinessEntityID_8 int NOT NULL,\r\n PersonType nchar(2) NOT NULL,\r\n NameStyle bit NOT NULL,\r\n Title nvarchar(8) NULL,\r\n FirstName nvarchar(50) NOT NULL,\r\n MiddleName nvarchar(50) NULL,\r\n LastName nvarchar(50) NOT NULL,\r\n Suffix nvarchar(10) NULL,\r\n EmailPromotion int NOT NULL,\r\n AdditionalContactInfo xml NULL,\r\n Demographics xml NULL,\r\n rowguid_19 uniqueidentifier NOT NULL,\r\n ModifiedDate_20 datetime NOT NULL,\r\n BusinessEntityID_21 int NOT NULL,\r\n AddressID_22 int NOT NULL,\r\n AddressTypeID_23 int NOT NULL,\r\n rowguid_24 uniqueidentifier NOT NULL,\r\n ModifiedDate_25 datetime NOT NULL,\r\n AddressID_26 int NOT NULL,\r\n AddressLine1 nvarchar(60) NOT NULL,\r\n AddressLine2 nvarchar(60) NULL,\r\n City nvarchar(30) NOT NULL,\r\n StateProvinceID int NOT NULL,\r\n PostalCode nvarchar(15) NOT NULL,\r\n SpatialLocation geography NULL,\r\n rowguid_33 uniqueidentifier NOT NULL,\r\n ModifiedDate_34 datetime NOT NULL,\r\n AddressTypeID_35 int NOT NULL,\r\n Name nvarchar(50) NOT NULL,\r\n rowguid_37 uniqueidentifier NOT NULL,\r\n ModifiedDate_38 datetime NOT NULL)\r\n<\/pre>\n… which you could execute successfully …<\/p>\n
DECLARE @CustomerAddresses TABLE\r\n (\r\n CustomerID INT NOT NULL, PersonID INT NULL, StoreID INT NULL, TerritoryID INT NULL,\r\n AccountNumber VARCHAR(10) NOT NULL, rowguid_6 UNIQUEIDENTIFIER NOT NULL,\r\n ModifiedDate_7 DATETIME NOT NULL, BusinessEntityID_8 INT NOT NULL,\r\n PersonType NCHAR(2) NOT NULL, NameStyle BIT NOT NULL, Title NVARCHAR(8) NULL, \r\n FirstName NVARCHAR(50) NOT NULL, MiddleName NVARCHAR(50) NULL, \r\n LastName NVARCHAR(50) NOT NULL,\r\n Suffix NVARCHAR(10) NULL, EmailPromotion INT NOT NULL,\r\n AdditionalContactInfo XML NULL, Demographics XML NULL,\r\n rowguid_19 UNIQUEIDENTIFIER NOT NULL, ModifiedDate_20 DATETIME NOT NULL,\r\n BusinessEntityID_21 INT NOT NULL, AddressID_22 INT NOT NULL, \r\n AddressTypeID_23 INT NOT NULL, rowguid_24 UNIQUEIDENTIFIER NOT NULL, \r\n ModifiedDate_25 DATETIME NOT NULL, AddressID_26 INT NOT NULL, \r\n AddressLine1 NVARCHAR(60) NOT NULL, AddressLine2 NVARCHAR(60) NULL, \r\n City NVARCHAR(30) NOT NULL, StateProvinceID INT NOT NULL, \r\n PostalCode NVARCHAR(15) NOT NULL, SpatialLocation GEOGRAPHY NULL, \r\n rowguid_33 UNIQUEIDENTIFIER NOT NULL, ModifiedDate_34 DATETIME NOT NULL,\r\n AddressTypeID_35 INT NOT NULL, Name NVARCHAR(50) NOT NULL, \r\n rowguid_37 UNIQUEIDENTIFIER NOT NULL, ModifiedDate_38 DATETIME NOT NULL\r\n );\r\nINSERT INTO @CustomerAddresses\r\n SELECT *\r\n FROM Sales.Customer\r\n INNER JOIN Person.Person\r\n ON Customer.PersonID = Person.BusinessEntityID\r\n INNER JOIN Person.BusinessEntityAddress\r\n ON Person.BusinessEntityID = BusinessEntityAddress.BusinessEntityID\r\n INNER JOIN Person.Address\r\n ON BusinessEntityAddress.AddressID = Address.AddressID\r\n INNER JOIN Person.AddressType\r\n ON BusinessEntityAddress.AddressTypeID = AddressType.AddressTypeID;\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"I\u2019ve 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\u2019t want your utilities intruding into the actual databases that you are developing or testing. The problem is that it doesn\u2019t work. Let me……<\/p>\n","protected":false},"author":154613,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[6813],"class_list":["post-83229","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83229","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/154613"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=83229"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83229\/revisions"}],"predecessor-version":[{"id":83339,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/83229\/revisions\/83339"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=83229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=83229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=83229"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=83229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}