Использование временных процедур

Tags: временные процедуры

Thursday, March 7, 2019 12:34:03 PM

На форумах часто описываются случаи, когда люди имеют специальные служебные базы данных со всеми их хранимыми процедурами и функциями для работы с базами данных на сервере. Это здорово, так как вы не хотите, чтобы ваши утилиты внедрялись в реальные базы данных, которые вы разрабатываете или тестируете.

Проблема в том, что это не работает. Позвольте продемонстрировать.

Допустим, у нас есть база данных MyDevStuff со всеми нашими замечательными инструментами. Вам нужно создать это. Затем мы вставляем фиктивную утилиту. В действительности это будут хитрые процедуры для создания отчетов, проверки проблемного кода, вывода содержимого таблиц, выполнения запросов к метаданным, создания документации и так далее.

Вместо этого мы создадим единственную процедуру, которая не делает ничего, кроме сообщения о контексте ее базы данных, и мы будем запускать ее на каждой базе данных в экземпляре. В то же время мы создадим идентичную временную процедуру.

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

Результат будет следующим:

 

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

 

Что ж, если смотреть с положительной стороны, процедура была правильной, когда она была в собственной базе данных. Но если процедура выполняется, она использует свой собственный контекст. Однако временная процедура использует любой контекст, в котором она выполняется. Это может быть очень полезно. Недавно я увидел комментарий о StackOverflow от пользователя, который сказал, что временные процедуры не имеют никакого смысла. Я не согласен

 

Это означает, что вы можете использовать только временные процедуры или зарегистрированные процедуры. Я должен объяснить, что вполне возможно создавать системные процедуры, функции и представления. Проблема с ними заключается в их развертывании. Они также навязчивы в основную базу данных, и вам действительно следует оставить основную базу данных в покое. Многие из нас используют их, но это определенно запах кода.

 

Можно ли создать временную процедуру внутри процедуры? Затем вы можете вызвать подпрограмму инициализации и избежать необходимости помещать весь фактический код вашей подпрограммы в пакет каждый раз, когда вы захотите его использовать.

 

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;

 

Да. Вопреки всем ожиданиям временная процедура трактуется иначе, чем временная таблица. Он не утилизируется в конце процедуры. Временная таблица должна быть очищена в конце процедуры, и вы можете увидеть, что это происходит здесь.



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

*/

 

Поскольку временные процедуры выполняются для всего сеанса или соединения, это означает, что вы можете использовать процедуру инициализации для создания утилит, которые вы создаете в начале соединения, и они будут продолжать существовать в течение всего срока жизни соединения. Эта процедура «InitStuff» является постоянной в вашей базе данных утилит, и, вызывая ее, вы либо обновляете, либо создаете свои временные процедуры.

 

Как всегда, есть загвоздка. Вы не можете создавать временные функции или представления. Это просто процедуры.

 

Все, что вам нужно сделать, это использовать команду USE в начале функции и указать контекст базы данных, в котором вы хотите, чтобы выполнялось тело подпрограммы.

 

Просто попробуйте!

 

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.

*/

Использование временных процедур для задач, которые требуют сценариев для разных баз данных требует меньше разрыва. Временные процедуры исчезают без трассировки, когда соединение или сеанс завершены. В коде можно создать их в течение сеанса, использовать их, а затем закрыть соединение. Можно было бы использовать подготовленные операторы, но на них нужно было бы ссылаться с помощью дескриптора переменной, и, наконец, от дескриптора пришлось бы избавиться: это неприятное осложнение.

 

Возникает раздражающая проблема с любой временной процедурой: это означает обновление скриптов, приложений и файлов при каждом изменении или улучшении процедуры. Не стоит иметь более одного источника. Если вы поддерживаете их из процедуры инициализации, хранящейся где-либо в экземпляре, то эта проблема содержится в следующем: вы просто изменяете процедуру инициализации в своей базе данных утилит из одного источника в системе управления версиями.



Отличный способ обойти все это - создать вид системы, функций или процедур. Это слишком навязчиво и редко разрешается. Представьте, что вы хотите сделать это на производственном сервере!

 

Вот пример утилиты, которая может потребоваться в качестве временной процедуры при разработке кода. Вы можете просто создать его в текущем окне запроса в SSMS. С этого момента вы можете использовать его для создания временных переменных, пока не закроете запрос

 

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

 

Затем вы можете выполнить это как

 

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';

Конечно, вы вряд ли захотите использовать этот запрос, но это всего лишь пример! Если бы вы использовали его, вы бы увидели как он работает, а затем смогли бы использовать следующий запрос.

 

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)

... который вы могли бы успешно выполнить ...

 

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;

No Comments

Add a Comment