• Статья

Проектирование хранимых процедур (компонент Database Engine)

Практически любой код Transact-SQL, который может быть записан в виде пакета, можно использовать для создания хранимой процедуры.

Правила проектирования хранимых процедур

Существуют следующие правила проектирования хранимых процедур:

  • Определение CREATE PROCEDURE само по себе может включать любое количество инструкций SQL любого типа, кроме перечисленных ниже. Они никогда не могут использоваться внутри хранимой процедуры.

    CREATE AGGREGATE

    CREATE RULE

    CREATE DEFAULT

    CREATE SCHEMA

    CREATE или ALTER FUNCTION

    CREATE или ALTER TRIGGER

    CREATE или ALTER PROCEDURE

    CREATE или ALTER VIEW

    SET PARSEONLY

    SET SHOWPLAN_ALL

    SET SHOWPLAN_TEXT

    SET SHOWPLAN_XML

    USE database_name

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

  • В хранимой процедуре можно обращаться к временным таблицам.

  • Если внутри хранимой процедуры создается локальная временная таблица, она может использоваться только в этой хранимой процедуре и будет удалена при выходе из нее.

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

  • При выполнении удаленной хранимой процедуры, которая производит изменения в удаленном экземпляре Microsoft SQL Server, нельзя произвести откат этих изменений. Удаленные хранимые процедуры не участвуют в транзакциях.

  • Максимальное количество параметров в хранимой процедуре — 2100.

  • Максимальное количество локальных переменных ограничено только доступной памятью.

  • В зависимости от доступной памяти максимальный размер хранимой процедуры может достигать 128 МБ.

Квалифицированные имена внутри хранимых процедур

В пределах хранимой процедуры имена объектов, указываемые в инструкциях (например, SELECT или INSERT), которые не содержат указания схемы, по умолчанию относятся к схеме хранимой процедуры. Если пользователь, который создает хранимую процедуру, не квалифицирует имя таблицы или представления, указываемое в инструкции SELECT, INSERT, UPDATE или DELETE внутри хранимой процедуры, доступ к нему через хранимую процедуру будет по умолчанию ограничен создателем процедуры.

Имена объектов, указываемых в любых DDL-инструкциях (CREATE, ALTER или DROP), инструкциях DBCC, а также EXECUTE и динамическом SQL, должны быть квалифицированы именем схемы объекта, если эту хранимую процедуру будут вызывать другие пользователи. Указание для этих объектов имени схемы служит гарантией того, что имя будет разрешено в тот же объект, независимо от того, кто вызвал хранимую процедуру. Если имя схемы не указано, SQL Server попытается разрешить имя объекта сначала в схеме по умолчанию пользователя, вызывающего хранимую процедуру указанного в предложении EXECUTE AS, а затем в схеме dbo.

Применение запутывания к определениям процедур

Для преобразования исходного текста инструкции CREATE PROCEDURE к запутанному формату применяется параметр WITH ENCRYPTION. Результат запутывания не виден непосредственно ни в одной системной таблице или представлении SQL Server 2008: пользователи, не имеющие доступа к системным представлениям, системным таблицам или файлам баз данных, не смогут получить текст, подвергнутый запутыванию. Однако этот текст будет доступен привилегированным пользователям, имеющим прямой доступ к файлам баз данных. Применив обратную процедуру, они смогут получить исходный текст определения хранимой процедуры.

Параметры инструкции SET

Компонент Database Engine при создании или изменении хранимой процедуры Transact-SQL сохраняет как параметр SET QUOTED_IDENTIFIER, так и параметр SET ANSI_NULLS. Эти первоначальные установки используются при выполнении хранимой процедуры. Следовательно, любые изменения параметров SET QUOTED_IDENTIFIER и SET ANSI_NULLS для сеанса пользователя при выполнении хранимой процедуры не учитываются. Инструкции SET QUOTED_IDENTIFIER и SET ANSI_NULLS, которые вызваны внутри хранимой процедуры, не оказывают никакого влияния на ее работу.

Остальные параметры SET, например SET ARITHABORT, SET ANSI_WARNINGS или SET ANSI_PADDINGS при создании или изменении хранимой процедуры не сохраняются. Если логика хранимой процедуры зависит от конкретного параметра, включите инструкцию SET в начало процедуры, чтобы гарантировать верную установку параметра. При выполнении инструкции SET внутри хранимой процедуры установка параметра остается в силе только до ее завершения. Затем в параметре восстанавливается значение, которое было до вызова хранимой процедуры. Это позволяет отдельным клиентам задавать нужные параметры, не влияя на логику ее работы.

ПримечаниеПримечание

Предупреждения ANSI_WARNINGS при передаче параметров хранимой процедуре, пользовательской функции или при объявлении и настройке переменных в инструкции пакетных заданий, игнорируются. Например, если объявить переменную как char(3), а затем присвоить ей значение длиннее трех символов, данные будут усечены до размера переменной, а инструкция INSERT или UPDATE завершится без ошибок.