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

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

SELECT @CurrentDatabase = Db_Name();

--next create an identical temporary procedure

CREATE OR ALTER PROCEDURE #WhatDatabaseAmIin @CurrentDatabase sysname OUTPUT

SELECT @CurrentDatabase = Db_Name();

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

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;

--now create a pretend utility that creates a tewmporary procedure

CREATE OR ALTER PROCEDURE InitStuff

IF Object_Id('tempdb..#ThisIsStrangeAndAlarming') IS NOT NULL

DROP PROCEDURE #ThisIsStrangeAndAlarming;

EXECUTE sp_executesql N'

CREATE procedure #ThisIsStrangeAndAlarming

@CurrentDatabase sysname OUTPUT

SELECT @CurrentDatabase = Db_Name();

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

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

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

USE @context

SELECT @CurrentDatabase = Db_Name();

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

**/

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

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

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;

SPBDEV Blog

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

No Comments

Роль привязок в Azure Functions

Создание Serverless API с помощью Azure Cosmos DB

Миграция базы данных с сервера Microsoft SQL Server on-premises в Azure SQL database