Sql процедуры: MS SQL Server и T-SQL

Содержание

Как правильно писать хранимые процедуры в SQL Server

Оригинал

Сегодня я бы хотел обсудить с вами тему хранимых процедур в SQL Server 2000-2005. В последнее время их написание занимало львиную долю моего времени на работе и чего уж тут скрывать – по окончанию работы с этим делом осталось достаточно информации, которой с удовольствием поделюсь с тобой %пользовательимя%.
Знания, которыми я собираюсь поделиться, к сожалению,(или к счастью) не добыты мной эмперически, а являются, в большей степени, вольным переводом некоторых статей из буржуйских интернетов.
Итак, как можно понять из названия речь пойдет об оптимизации. Сразу оговорюсь, что все действия, которые я сейчас буду описывать, действительно дают существенный(некоторые больший, некоторые меньший) прирост производительности.
Данная статья не претендует на полное раскрытие темы оптимизации, скорее это собрание практик, которые я применяю в своей работе и могу ручаться за их эффективность. Поехали!

Включай в свои процедуры строку — SET NOCOUNT ON:

С каждым DML выражением, SQL server заботливо возвращает нам сообщение содержащее колличество обработанных записей.

Данная информация может быть нам полезна во время отладки кода, но после будет совершенно бесполезной. Прописывая SET NOCOUNT ON, мы отключаем эту функцию. Для хранимых процедур содержащих несколько выражений или\и циклы данное действие может дать значительный прирост производительности, потому как колличество трафика будет значительно снижено.

CREATE PROC dbo.ProcName AS SET NOCOUNT ON; —Здесь код процедуры SELECT column1 FROM dbo.TblTable1 —Перключение SET NOCOUNT в исходное состояние SET NOCOUNT OFF; GO

CREATE PROC dbo.ProcName

SET NOCOUNT ON;

—Здесь код процедуры

SELECT column1 FROM dbo.TblTable1

—Перключение SET NOCOUNT в исходное состояние

SET NOCOUNT OFF;

Используй имя схемы с именем объекта:

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

SELECT * FROM dbo.MyTable —Вот так делать хорошо — Вместо SELECT * FROM MyTable —А так делать плохо —Вызов процедуры EXEC dbo.MyProc —Опять же хорошо —Вместо EXEC MyProc —Плохо!

SELECT * FROM dbo.MyTable —Вот так делать хорошо

— Вместо

SELECT * FROM MyTable —А так делать плохо

—Вызов процедуры

EXEC dbo.MyProc —Опять же хорошо

—Вместо

EXEC MyProc —Плохо!

Не используй префикс «sp_» в имени своих хранимых процедур:

Если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных. Дело в том, что данный префикс используется для личных внутренних хранимых процедур сервера. Поэтому его использование может привести к дополнительным расходам и даже неверному результату, если процедура с таким же имененем как у вас будет найдена в его базе.

Используй IF EXISTS (SELECT 1) вместо IF EXISTS (SELECT *):

Чтобы проверить наличие записи в другой таблице, мы используем выражение IF EXISTS. Данное выражение возвращает true если из внутреннего выражения возвращается хоть одно изначение, не важно «1», все колонки или таблица. Возращаемые данные, в принципе никак не используются. Таким образом для сжатия трафика во время передачи данных логичнее использовать «1», как показано ниже:

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘MyTable’ AND type = ‘U’)

IF EXISTS (SELECT 1 FROM sysobjects

WHERE name = ‘MyTable’ AND type = ‘U’)

Используй TRY-Catch для отлова ошибок:

До 2005 сервера после каждого запроса в процедуре писалось огромное колличество проверок на ошибки. Больше кода всегда потребляет больше ресурсов и больше времени. С 2005 SQL Server’ом появился более правильный и удобный способ решения этой проблемы:

BEGIN TRY —код END TRY BEGIN CATCH —код отлова ошибки END CATCH

BEGIN TRY

—код

END TRY

BEGIN CATCH

—код отлова ошибки

END CATCH

Facebook

Twitter

Вконтакте

Google+

Вред хранимых процедур / Блог компании RUVDS.

com / Хабр
В чат подкаста «Цинковый прод» скинули статью о том, как некие ребята перенесли всю бизнес-логику в хранимые процедуры на языке pl/pgsql. И так как у статьи было много плюсов, то значит, есть люди, а может быть, их даже большинство, которые положительно восприняли такой рефакторинг.

Я не буду растекаться мысью по древу, а сразу накидаю кучку минусов использования хранимых процедур.

Минусы хранимых процедур

Версионирование

Если в случае с кодом на php вы можете просто переключиться в git на другую ветку и посмотреть, что получилось, то хранимые процедуры нужно еще засунуть в базу. И традиционные миграции тут плохо помогут: если записывать все изменения хранимок как новый CREATE OR REPLACE PROCEDURE, то на кодревью будет ад: всегда новый файл, который непонятно с чем сравнивать. Поэтому придется искать какие-то дополнительные инструменты или писать свой велосипед.

Сам язык pl/pgsql

Это устаревший процедурный язык из девяностых, который вообще никак не развивается.

Никакого ООП или ФП или чего бы то ни было. Синтаксис без малейшего намека на синтаксический сахар.

Например, переменные нужно объявлять в начале процедуры, в специальном блоке DECLARE. Так делали наши деды, в этом есть некая ностальгия по языку Pascal, но спасибо, не в 2020.

Сравните две функции, которые делают одно и то же на php и pl/pgsql:

CREATE OR REPLACE FUNCTION sum(x int, y int)
    RETURNS int
    LANGUAGE plpgsql
AS $$
DECLARE
    result int;
BEGIN
    result := x + y;
    return result;
END;
$$;

function sum(int $x, int $y): int
{
    $result = $x + $y;
    return $result;
}

Примерно в 2-3 раза больше писанины.

Кроме того, язык интерпретируемый, без JIT и т.д. (поправьте меня, если что-то изменилось в последних версиях). Т.е. все очень медленно и печально. Уж если использовать какие-то хранимки, то на чистом SQL или v8 (т.е. javascript).

Отладка

Поверьте, отлаживать код на php в 100500 раз проще.

Ты просто поправил что-то и смотришь результат. Можно обложить echo или смотреть, что там через xdebug прямо в IDE.

Отладка хранимых процедур — это неудобно. Это надо делать в pgadmin (включив специальное расширение). PgAdmin — это далеко не PHPstorm по удобству.

Логирование и обработка ошибок

Забудьте о том, чтобы красивый json c трейсом падал с stdout, а потом в graylog и в sentry. И чтобы все это автоматически происходило, выдавая пользователю ошибку 500, в случае если контроллер не поймал exception.

В хранимках pl/pgsql вы всё будете делать вручную:

GET DIAGNOSTICS stack = PG_CONTEXT; RAISE NOTICE E'--- Стек вызова ---\n%', stack;

Сбор метрик

Вы не можете, как в golang, просто добавить эндпоинт /metrics, который будет подсасываться Прометеусом, куда вы напихаете бизнесовые и другие метрики для мониторинга. Я просто не знаю, как тут выкрутиться с pl/pgsql.

Масштабирование

Выполнение хранимых процедур тратит ресурсы (например, CPU) сервера базы данных.

В случае других языков вы можете вынести логику на другие ноды.

Зависимости

В php вы, используя пакетный менеджер composer, одним движением можете подтянуть нужную библиотеку из интернета. Точно так же как в js это будет npm, в Rust это будет cargo и т.д.

В мире pl/pgsql нужно страдать. В этом языке просто нет менеджера зависимостей.

Фреймворки

В современном мире веб-приложение часто не пишут с нуля, а собирают на основе фреймворка, используя его компоненты. К примеру, на Laravel у вас из коробки есть роутинг, валидация запроса, движок шаблонов, аутентификация/авторизация, 100500 хелперов на все случаи жизни и т.д. Писать всё это вручную с нуля, на устаревшем языке — ну нет, спасибо.

Получится много велосипедов, которые потом еще и поддерживать придется.

Юнит-тесты

Сложно даже представить, как удобно организовать unit-тесты в хранимках на pl/pgsql. Я ни разу не пробовал. Поделитесь пожалуйста в комментариях.

Рефакторинг

Несмотря на то, что существует IDE для работы с базой данных (Datagrip), для обычных языков средства рефакторинга гораздо богаче.

Всевозможные линтеры, подсказки по упрощению кода и т.д.

Маленький пример: в тех кусках кода, которые я привел в начале статьи, PHPStorm дал подсказку, что переменная $result необязательна, и можно просто сделать

return $x + $y;

В случае с plpgsql — тишина.

Плюсы хранимых процедур

Нет оверхеда на перегон промежуточных данных по пути бекенд-БД.
В хранимых процедурах кешируется план запроса, что может сэкономить пару ms. т.е. как обертка над запросом иногда это имеет смысл делать (в редких случаях и не на pl/pgsql, а на голом sql), если бешеный хайлоад, а сам запрос выполняется быстро.
Когда пишешь свой extension к посгресу — без хранимок не обойтись.
Когда хочешь из соображений безопасности спрятать какие-то данные, дав доступ приложению только к одной-двум хранимкам (редкий кейс).

Выводы

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

В остальных кейсах — вы только усложните жизнь разработчикам, причем существенно.

Я бы понял, если в исходной статье часть логики переложили на SQL, это можно понять. Но зачем хранимки — это загадка.

Буду рад, если вы считаете, что я неправ или знаете, какие-то еще ситуации, связанные с хранимыми процедурами (как плюсы, так и минусы), и напишете об этом в коменты.

sql-docs.ru-ru/execute-a-stored-procedure.md at live · MicrosoftDocs/sql-docs.ru-ru · GitHub

title

description

ms.custom

ms.date

ms.prod

ms.reviewer

ms.technology

ms.topic

f1_keywords

helpviewer_keywords

ms.assetid

author

ms.author

monikerRange

ms.openlocfilehash

ms.sourcegitcommit

ms.translationtype

ms.contentlocale

ms.lasthandoff

ms.locfileid

Выполнение хранимой процедуры | Документация Майкрософт

Узнайте, как в SQL Server 2019 (15. x) выполнить хранимую процедуру с помощью SQL Server Management Studio или Transact-SQL.

03/16/2017

sql

stored-procedures

conceptual

sql13.swb.executeprocedure.general.f1

sql13.swb.executeprocedure.f1

stored procedures [SQL Server], parameters

extended stored procedures [SQL Server], executing

system stored procedures [SQL Server], executing

stored procedures [SQL Server], executing

user-defined stored procedures [SQL Server]

a0b1337d-2059-4872-8c62-3f967d8b170f

stevestein

sstein

>=aps-pdw-2016||=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

115220ef0fabbe98a0c4a1bf5f185409c5a82829

38e055eda82d293bf5fe9db14549666cf0d0f3c0

ru-RU

02/02/2021

99250288

[!INCLUDESQL Server Azure SQL Database Synapse Analytics PDW ]

В этом разделе описывается, как выполнить хранимую процедуру [!INCLUDEssnoversion] при помощи среды [!INCLUDEssManStudioFull] или [!INCLUDEtsql].

Существует два способа выполнения хранимой процедуры. Первым и наиболее распространенным подходом является вызов процедуры приложением или пользователем. Второй подход — настройка автоматического выполнения процедуры при запуске экземпляра [!INCLUDEssNoVersion] . Если процедура вызывается приложением или пользователем, то в вызове явно указывается ключевое слово [!INCLUDEtsql] EXECUTE или EXEC. Процедуру также можно вызывать и выполнять без ключевого слова, если она является первой инструкцией в пакете [!INCLUDEtsql] .

В этом разделе

Перед началом

Ограничения

При сопоставлении имен системных процедур используются параметры сортировки вызывающей базы данных. Таким образом, в вызове процедур следует всегда использовать точный регистр имен системных процедур. Например, этот код завершится с ошибкой при выполнении в контексте базы данных, в параметрах сортировки которой учитывается регистр:
```
EXEC SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help  
```
Чтобы показать точные имена системных процедур, запросите представления каталога sys. system_objects и sys.system_parameters .
Если определяемая пользователем процедура имеет имя, совпадающее с системной процедурой, то такая определяемая пользователем процедура никогда не будет выполняться.

безопасность

Дополнительные сведения см. в статьях EXECUTE AS (Transact-SQL) и EXECUTE AS Clause (Transact-SQL).

Permissions

Дополнительные сведения см. в разделе «Разрешения» статьи EXECUTE (Transact-SQL).

Использование среды SQL Server Management Studio

Выполнение хранимой процедуры

В обозревателе объектов подключитесь к экземпляру компонента [!INCLUDEssDEnoversion], разверните его, а затем разверните узел Базы данных.
Разверните нужную базу данных, разверните узлы Программирование и Хранимые процедуры.
Щелкните правой кнопкой мыши определяемую пользователем хранимую процедуру и выберите команду Выполнить хранимую процедуру.
В диалоговом окне Выполнение процедуры укажите значение для каждого параметра и необходимость передачи значения NULL.
Параметр
Указывает имя параметра.
Тип данных
Указывает тип данных параметра.
Выходной параметр
Указывает, является ли этот параметр выходным.
Передать значение NULL
Передать значение NULL в качестве значения параметра.
Значение
Введите значение параметра, передаваемое ему при вызове процедуры.
Чтобы выполнить хранимую процедуру, нажмите кнопку ОК.

Использование Transact-SQL

Выполнение хранимой процедуры

Установите соединение с компонентом [!INCLUDEssDE].
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как выполнить хранимую процедуру, которая принимает один параметр. В примере выполняется хранимая процедура uspGetEmployeeManagers со значением 6 , указанным в параметре @EmployeeID .

USE AdventureWorks2012;  
GO  
EXEC dbo.uspGetEmployeeManagers 6;  
GO

Установка и отмена автоматического запуска процедуры

Установите соединение с компонентом [!INCLUDEssDE].
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как использовать процедуру sp_procoption , чтобы задать автоматическое выполнение процедуры.

USE AdventureWorks2012;  
GO  
EXEC sp_procoption @ProcName = '<procedure name>'   
    , @OptionName = 'startup'   
    , @OptionValue = 'on';

Отмена автоматического выполнения процедуры

Установите соединение с компонентом [!INCLUDEssDE].
На панели «Стандартная» нажмите Создать запрос.
Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере показано, как использовать процедуру sp_procoption , чтобы отменить автоматическое выполнение процедуры.

USE AdventureWorks2012;  
GO  
EXEC sp_procoption @ProcName = '<procedure name>'   
    , @OptionValue = 'off';

Примеры (Transact-SQL)

См. также:

Указание параметров
Настройка параметра конфигураци и сервера scan for startup procs
EXECUTE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
Хранимые процедуры (компонент Database Engine)

Как сохранить текст хранимой процедуры SQL Server’а

Иногда я прошу прислать текст хранимой процедуры SQL сервера. Не все знают, как это сделать и вот краткая инструкция с картинками 🙂

Сначала откройте Microsoft SQL Server Management Studio. Для того, чтобы подсоединиться к серверу баз данных, Вы должны либо знать пароль системного пользователя SQL sa, либо, Ваша учётная запись должна позволять входить при Windows авторизации. Иногда при входе на SQL сервер окно подробностей выключено, чтобы его открыть проделайте следующие действия: выберите «View->Object Explorer Details»

Далее последовательно раскройте папки «Databases», название требуемой базы данных, например, «scalaDB1», внутри неё «Programmability» и нажмите на папку «Stored Procedures»:

Список хранимых процедур отобразится в окне «Object Explorer Details». Иногда некоторые хранимые процедуры зашифрованы, в этом случае Вы не сможете их просмотреть и сохранить. На картинке выше такие хранимые процедуры снабжены пиктограммкой с замком. Найдите требуемую хранимую процедуру и щёлкните на её названии правой кнопкой мыши, затем последовательно выберите из всплывающих контекстных меню «Script Stored Procedure as -> CREATE To -> File…»

После этого задайте расположение и название файла:

А после сохранения файла он будет доступен для просмотра, например, с помощью Блокнота:

Этот файл и нужно переслать тому, кто его у Вас попросил 🙂

Все материалы раздела iScala «для чайников»:

Что такое проводки 07 типа модуля «Управление Запасами»?
Типы проводок Главной Книги :: General Ledger Transaction Types
Что такое проводки 10 типа модуля Управление Запасами?
Цикл продаж
Автоучет и Книга Закупок
Коварный параметр «Учет запасов»
Импорт информации в iScala — это очень просто!
«Умножение» скидок в Заказах на Продажу 2. 0
Взаимодействие скидок в Заказах на Продажу
Ввод прихода для заказа на закупку
Что такое автоучет и как это работает?
Мы поменяли пароль sa и теперь у нас не работают отчёты AFR и SSRS. Что делать?
Мы учитываем напитки в литрах, хотим изменить единицу измерения, как это сделать самым простым способом?
AFR: что это такое, из чего состоит, с чем взаимодействует? :: AFR: what is it, what does it consist of, what it interacts with?
Мы случайно удалили проводку в журнале счетов-фактур Книги Закупок, что делать?
Ещё раз об импорте информации в iScala
У нас для закупок используется единица измерения упаковка, а для склада — килограммы, как это правильно ввести в карточке запаса?
Стандартная последовательность действий при работе с модулем «Заказ на Закупку»
Standard steps when working with the «Purchase Order» module
Предполагаемые действия при работе с модулем «Управление Запасами»
Что такое «непрерывная консолидация» и с чем её едят?
Сравнение разных технологий печати документов в Scala, iScala
Как сохранить текст хранимой процедуры SQL Server’а
Мы перешли на новую версию iScala и у нас перестали работать внешние отчёты в Excel, что делать?
Scala/iScala и электронная отчётность в России | Scala/iScala and e-reporting in Russia
Шаги по получению запасов от поставщика по заказу на закупку без лишних подробностей
iScala 3. 4 проверяет наличие требуемых DDF файлов до того, как вывести список выходных каналов для печати

MS SQL Server. Принудительная перекомпиляция хранимых процедур

Даже если вы не работали с MS SQL Server, для вас не будет неожиданностью тот факт, что план выполнения хранимой процедуры (и не только ее) вычисляется лишь однажды, после чего он кешируется и используется со всеми последующими входными параметрами. Обычно это поведение — это то, чего мы ожидаем. Но иногда такое поведение приводит к тому, что план запроса, являющийся оптимальным для изначальных параметров, может оказаться не оптимальным для другого набора параметров. Как решить подобную проблему?

-- Исходная хранимая процедура
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
GO

-- ------------------
-- 1 вариант: Использование DBCC FREEPROCCACHE
-- Выполнение этой инструкции каждый раз будет очищать кеш планов запросов. 
-- Эта инструкция не очищает план нативно-скомпилированных хранимых процедур.
-- Подробнее о синтаксисе можно почитать по ссылке:
-- https://msdn.microsoft.com/en-us/library/ms174283.aspx
DBCC FREEPROCCACHE
GO
EXEC usp_countrysearch 'UK'

-- ------------------
-- 2 вариант: Использование опции WITH RECOMPILE команды EXEC.
-- При каждом запуске хранимой процедуры с данной опцией будет производиться перекомпиляция:
EXEC usp_contrysearch 'UK' WITH RECOMPILE
GO

-- ------------------
-- 3 вариант: Использование опции RECOMPILE при создании хранимой процедуры,
-- после чего все запросы на исполнение будут приводить к ее принудительной перекомпиляции 
-- (даже без указания EXEC WITH RECOMPILE):
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
OPTION (RECOMPILE)
GO

-- ------------------
-- 4 вариант: Строго говоря, этот вариант не относится к принудительной перекомпиляции.  
-- Это вариант предварительной оптимизации, но я о нем все равно упомяну.
-- Если мы имеем известный набор планов для хранимой процедуры
-- и набор значений входных параметров для каждого из них, то мы можем выбрать наиболее оптимальный 
-- и скомпилировать хранимую процедуру так, чтобы всегда использовался этот план:
ALTER PROCEDURE [dbo].[usp_contrysearch]
@country varchar(80)
AS
SELECT p.lastname, p.dob, p.sex, c.country
FROM people p join country c
ON p.personid = c.personid
WHERE c.country = @country
OPTION (OPTIMIZE FOR (@country = 'UK')) 
-- теперь для любых входных параметров будет использоаться план как для @country = 'UK'
GO

СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР В MICROSOFT SQL SERVER

Для освоения программирования хранимых процедур используем при- мер базы данных c названием DB_Books, которая была создана в лабора- торной работе №1. При выполнении примеров и заданий обращайте вни- мание на соответствие названий БД, таблиц и других объектов проекта.

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

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

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

Временные хранимые процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Они делятся на ло- кальные и глобальные. Локальные временные хранимые процедуры могут быть вызваны только из того соединения, в котором созданы. При созда- нии такой процедуры ей необходимо дать имя, начинающееся с одного символа #. Как и все временные объекты, хранимые процедуры этого типа автоматически удаляются при отключении пользователя, перезапуске или остановке сервера. Глобальные временные хранимые процедуры доступны для любых соединений сервера, на котором имеется такая же процедура. Для ее определения достаточно дать ей имя, начинающееся с символов ##. Удаляются эти процедуры при перезапуске или остановке сервера, а также при закрытии соединения, в контексте которого они были созданы.

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

Синтаксис оператора создания новой или изменения имеющейся хранимой процедуры в обозначениях MS SQL Server:

{CREATE | ALTER } PROC[EDURE] имя_процедуры [;номер]
[{@имя_параметра тип_данных } [VARYING ] [=DEFAULT][OUTPUT] ][,...n]
[WITH { RECOMPILE | ENCRYPTION | RECOMPILE,
ENCRYPTION }]
[FOR REPLICATION]
AS
sql_оператор [...n]

Рассмотрим параметры данной команды.

Используя префиксы sp_, #, ##, создаваемую процедуру можно определить в качестве системной или временной. Как видно из синтаксиса команды, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, где она должна быть размещена. Таким образом, чтобы разместить создаваемую хранимую процедуру в конкретной базе данных, необходимо выполнить команду CREATE PROCEDURE в контексте этой базы данных. При обращении из тела хранимой процедуры к объектам той же базы данных можно использовать укороченные имена, т. е. без указания имени базы данных. Когда же требуется обратиться к объектам, расположенным в других базах данных, указание имени базы данных обязательно.

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

Наличие ключевого слова OUTPUT означает, что соответствующий параметр предназначен для возвращения данных из хранимой процедуры. Однако это вовсе не означает, что параметр не подходит для передачи значений в хранимую процедуру. Указание ключевого слова OUTPUT предписывает серверу при выходе из хранимой процедуры присвоить текущее значение параметра локальной переменной, которая была указана при вызове процедуры в качестве значения параметра. Отметим, что при указании ключевого слова OUTPUT значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Не разрешается использование любых выражений или констант, допустимое для обычных параметров. Ключевое слово VARYING применяется совместно с параметром OUTPUT, имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.

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

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

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию. Ключевое слово ENCRYPTION предписывает серверу выполнить шифрование кода хранимой процедуры, что может обеспечить защиту от использования авторских алгоритмов, реализующих работу хранимой процедуры. Ключевое слово AS размещается в начале собственно тела хранимой процедуры. В теле процедуры могут применяться практически все команды SQL, объявляться транзакции, устанавливаться блокировки и вызываться другие хранимые процедуры. Выход из хранимой процедуры можно осуществить посредством команды RETURN.

DROP PROCEDURE {имя_процедуры} [,...n]

Для выполнения хранимой процедуры используется команда:
[[ EXEC [ UTE] имя_процедуры [;номер]
[[@имя_параметра=]{значение | @имя_переменной}
[OUTPUT ]|[DEFAULT ]][,...n]

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

Использование ключевого слова OUTPUT при вызове процедуры раз- решается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.

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

Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение по умолчанию, просто пропустив его при перечислении, нельзя. Если же тре- буется опустить параметры, для которых определено значение по умолча- нию, достаточно явного указания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произвольном порядке.

Отметим, что при вызове процедуры указываются либо имена пара- метров со значениями, либо только значения без имени параметра. Их комбинирование не допускается.

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

CREATE PROCEDURE Count_Books AS
SELECT COUNT(Code_book) FROM Books
GO

Задание 1. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books

Проверьте результат.

Пример создания процедуры c входным параметром:

CREATE PROCEDURE Count_Books_Pages @Count_pages AS INT
AS
SELECT COUNT(Code_book) FROM Books WHERE Pages>=@Count_pages
GO

Задание 2. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books_Pages 100

Проверьте результат.

Пример создания процедуры c входными параметрами:

CREATE PROCEDURE Count_Books_Title @Count_pages AS INT, @Title AS
CHAR(10)
AS
SELECT COUNT(Code_book) FROM Books WHERE Pages>=@Count_pages
AND Title_book LIKE @Title
GO

Задание 3. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC Count_Books_Title 100, 'П%'

Проверьте результат.

Пример создания процедуры c входными параметрами и выходным параметром:

CREATE PROCEDURE Count_Books_Itogo @Count_pages INT, @Title
CHAR(10) , @Itogo INT OUTPUT
AS
SELECT @Itogo = COUNT(Code_book) FROM Books WHERE
Pages>=@Count_pages AND Title_book LIKE @Title
GO

Задание 4. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите с помощью набора команд:

 sql>
Declare @q As int
EXEC Count_Books_Itogo 100, 'П%', @q output
select @q

Проверьте результат.

Пример создания процедуры c входными параметрами и RETURN:

CREATE PROCEDURE checkname @param INT
AS
IF (SELECT Name_author FROM authors WHERE Code_author = @param) =
'Пушкин А.С.'
RETURN 1
ELSE
RETURN 2

Задание 5. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

DECLARE @return_status INT
EXEC @return_status = checkname 1
SELECT 'Return Status' = @return_status

Пример создания процедуры без параметров для увеличения значения ключевого поля в таблице Purchases в 2 раза:

CREATE PROC update_proc
AS
UPDATE Purchases SET Code_purchase = Code_purchase*2

Процедура не возвращает никаких данных.

Задание 6. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команды

EXEC update_proc

Пример процедуры с входным параметром для получения всей ин- формации о конкретном авторе:

CREATE PROC select_author @k CHAR(30)
AS
SELECT * FROM Authors WHERE name_author=@k

Задание 7. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

EXEC select_author 'Пушкин А.С.' или
select_author @k='Пушкин А.С.' или
EXEC select_author @k='Пушкин А.С.'

Пример создания процедуры с входным параметром и значением по умолчанию для увеличения значения ключевого поля в таблице Purchases в заданное количество раза (по умолчанию в 2 раза):

CREATE PROC update_proc @p INT = 2
AS
UPDATE Purchases SET Code_purchase = Code_purchase *@p

Процедура не возвращает никаких данных.

Задание 8. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

EXEC update_proc 4 или
EXEC update_proc @p = 4 или
EXEC update_proc --будет использовано значение по умолчанию.

Пример создания процедуры с входным и выходным параметрами. Создать процедуру для определения количества заказов, совершенных за указанный период:

CREATE PROC count_purchases
@d1 SMALLDATETIME, @d2 SMALLDATETIME,
@c INT OUTPUT
AS
SELECT @c=COUNT(Code_purchase) FROM Purchases WHERE Date_order
BETWEEN @d1 AND @d2
SET @c = ISNULL(@c,0)

Задание 9. Создайте данную процедуру в разделе Stored Procedures ба- зы данных DB_Books через утилиту SQL server Management Studio. Запус- тите ее с помощью команд:

DECLARE @c2 INT
EXEC count_purchases ’01-jun-2006’, ’01-jul-2006’, @c2 OUTPUT
SELECT @c2

Варианты заданий к лабораторной работе №4

Общие положения. В утилите SQL Server Management Studio создать новую страницу для кода (кнопка «Создать запрос»). Программно сделать активной созданную БД DB_Books с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые были выполнены во второй лабораторной работе. Причем код SQL запросов нужно изменить таким образом, чтобы в них можно было передавать значения полей, по которым осуществляется поиск.

Например, исходное задание и запрос в лабораторной работе №2:

/*Выбрать из справочника поставщиков (таблица Deliveries) названия ком- паний, телефоны и ИНН (поля Name_company, Phone и INN), у которых название компании (поле Name_company) „ОАО МИР“.

SELECT Name_company, Phone, INN FROM Deliveries
WHERE Name_company = 'ОАО МИР'

*/ –В данной работе будет создана процедура:

CREATE PROC select_name_company @comp CHAR(30)
AS
SELECT Name_company, Phone, INN FROM Deliveries
WHERE Name_company = @comp

–Для запуска процедуры используется команда:

EXEC select_name_company 'ОАО МИР'

Сохранить файл программы с названием ФамилияСтудента_ЛАб_4. В SQL Server Management Studio в разделе хранимых процедур БД DB_Books проверить наличие процедур.

Список заданий

В утилите SQL Server Management Studio создать новую программу. Программно сделать активной индивидуальную БД, созданную в лабораторной работе №1, с помощью оператора Use. Создать хранимые процедуры c помощью операторов Create procedure, причем самостоятельно определить имена процедур. Каждая процедура будет выполнять по одному SQL запросу, которые представлены в виде отдельных заданий по вариантам.

Сохранить файл программы с названием Фамилия Студента_Лаб_4_№варианта. В SQL Server Management Studio в разделе хранимых процедур индивидуальной БД проверить наличие процедур.

Вариант 1

1. Вывести список сотрудников, у которых есть хотя бы один ребенок.

2. Вывести список детей, которым выдали подарки в указанный период.

3. Вывести список родителей, у которых есть несовершеннолетние дети.

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

Вариант 2

1. Вывести список приборов с указанным типом.

2. Вывести количество отремонтированных приборов и общую стоимость ремонтов у указанного мастера.

3. Вывести список владельцев приборов и количество их обращений, отсортированный по количеству обращений по убыванию.

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

Вариант 3

1. Вывести список цветков с указанным типом листа.

2. Вывести список кодов продаж, по которым продано цветов на сумму больше указанного числа.

3. Вывести дату продажи, сумму, продавца и цветок по указанному коду продажи.

4. Вывести список цветов и сорт для цветов с высотой больше указанного числа или цветущий.

Вариант 4

1. Вывести список лекарств с указанным показанием к применению.

2. Вывести список дат поставок, по которым продано больше указанного числа одноименного лекарства.

3. Вывести дату поставки, сумму, ФИО руководителя от поставщика и название лекарства по коду поступления больше указанного числа.

4. Вывести список лекарств и единицы измерения для лекарств с количеством в упаковке больше указанного числа или кодом лекарства меньше определенного значения.

Вариант 5

1. Вывести список сотрудников с указанной должностью.

2. Вывести список списанного оборудования по указанной причине.

3. Вывести дату поступления, название оборудования, ФИО ответственного и дату списания для оборудования, списанного в указанный период.

4. Вывести список оборудования с указанным типом или с датой поступления больше определенного значения

Вариант 6

1. Вывести список блюд с весом больше указанного числа.

2. Вывести список продуктов, в названии которых встречается указанный фрагмент слова.

3. Вывести объем продукта, название блюда, название продукта с кодом блюда от указанного начального значения по определенному конечному значению.

4. Вывести порядок приготовления блюда и название блюда с количеством углеводов больше определенного значения или количеством калорий больше указанного значения.

Вариант 7

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО регистратора и название организации для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с определенным типом документа или с датой регистрации больше указанного значения.

Вариант 8

1. Вывести список сотрудников с указанной причиной увольнения.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, причину увольнения, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 9

1. Вывести список сотрудников, бравших отпуск указанного типа.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, тип отпуска, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 10

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО отправителя и название организации для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа меньше определенного значения.

Вариант 11

1. Вывести список сотрудников, назначенных на указанную должность.

2. Вывести список документов с датой регистрации в указанный период.

3. Вывести дату регистрации, должность, ФИО сотрудника для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с кодом документа в указанном диапазоне.

Вариант 12

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированный по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 13

1. Вывести список оборудования с указанным типом. 2. Вывести список оборудования, которое списал определенный сотрудник.

3. Вывести количество списанного оборудования, сгруппированного по типам оборудования.

4. Вывести информацию о сотрудниках с датой приема на работу больше определенной даты.

Вариант 14

1. Вывести список цветков с указанным типом листа.

2. Вывести список кодов поступлений, по которым продано цветов на суммы больше определенного значения.

3. Вывести дату поступления, сумму, названия поставщика и цветов по определенному коду поставщика.

4. Вывести список цветов и сорт для цветов с высотой больше определенного числа или цветущий.

Вариант 15

1. Вывести список клиентов, заехавших в номера в указанный период.

2. Вывести общую сумму оплат за номера для каждого клиента.

3. Вывести дату заезда, тип номера, ФИО клиентов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных клиентов в номерах определенного типа.

Вариант 16

1. Вывести список оборудования с указанным типом.

2. Вывести список оборудования, которое брал в прокат определенный клиент.

3. Вывести список лиц, бравших оборудование в прокат и количество их обращений, отсортированных по количеству обращений по убыванию.

4. Вывести информацию о клиентах, отсортированных по адресам.

Вариант 17

1. Вывести список ценностей с закупочной стоимостью больше определенного значения или сроком гарантии больше указанного числа.

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

3. Вывести сумму стоимости ценностей с кодом в указанном диапазоне.

4. Вывести список материально ответственных лиц с датой приема на работу в указанном диапазоне.

Вариант 18

1. Вывести список ремонтных работ, выполненных определенным мастером.

2. Вывести список этапов работ, входящих в работы, в названии которых встречается указанное слово.

3. Вывести сумму стоимости этапов ремонтных работ для работ с кодом в указанном диапазоне.

4. Вывести список мастеров с датой приема на работу в указанном диапазоне.

Вариант 19

1. Вывести список лекарств с определенным показанием.

2. Вывести список номеров чеков, по которым продано больше определенного числа лекарств.

3. Вывести дату продажи, сумму, ФИО кассира и лекарство по чеку с указанным номером.

Вариант 20

1. Вывести список сотрудников с указанной должностью.

2. Вывести список документов, в содержании которых встречается указанный фрагмент слова.

3. Вывести дату регистрации, тип документа, ФИО исполнителя и факт исполнения для документов, зарегистрированных в указанный период.

4. Вывести список зарегистрированных документов с указанным типом документа или с кодом документа в определенном диапазоне.

Назад: СОЗДАНИЕ ХРАНИМЫХ ПРОЦЕДУР В MICROSOFT SQL SERVER

SPBDEV Blog — Создание динамического SQL в хранимой процедуре

Tags: SQL

Как построить динамический SQL в хранимой процедуре

После прочтения этой статьи вы поймете основы динамического SQL; как создавать инструкции на основе значений переменных и как выполнять эти сконструированные инструкции, используя sp_executesql и EXECUTE () из хранимой процедуры.

Все примеры этого урока основаны на Microsoft SQL Server Management и образцов баз данных, AdventureWorks и WideWorldImporters.

Создание динамического SQL в хранимой процедуре

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

Ниже приведен пример статического SQL:

SELECT JobTitle, Count(BusinessEntityID)

FROM HumanResources.Employee

WHERE Year(BirthDate) = 1970

GROUP BY JobTitle

SELECT JobTitle, Count(BusinessEntityID)

FROM HumanResources.Employee

WHERE Year(BirthDate) = 1971

GROUP BY JobTitle

Обратите внимание, что есть две инструкции, каждая из которых возвращает резюме JobTitles для конкретного года рождения сотрудника. Если мы хотим добавить больше лет рождения, нам нужно добавить больше инструкций. Что нужно сделать, чтобы написать инструкцию единожды и изменять год на лету?

Именно здесь вступает в игру динамический SQL.

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

Затем в этих переменных выполняется код. Продолжая наш пример, вот тот же код с использованием динамического SQL:

DECLARE @birthYear int = 1970

DECLARE @statement NVARCHAR(4000)

WHILE @birthYear <= 1971

BEGIN

SET @statement = ‘

SELECT JobTitle, Count(BusinessEntityID)

FROM HumanResources.Employee

WHERE Year(BirthDate) = ‘ + CAST(@birthYear as NVARCHAR) +

‘ GROUP BY JobTitle’

EXECUTE sp_executesql @statement

SET @birthYear = @birthYear + 1

END

Динамический SQL выделен жирным шрифтом. Это SQL, который построен для каждого @birthYear. По мере создания SQL он сохраняется в @statement. Затем он выполняется с использованием sp_executesql, который мы объясним ниже.

Введение в sp_executesql

Вы можете использовать sp_executeslq для выполнения транзакционного SQL, хранящегося в переменной. Форма инструкции:

EXECUTE sp_executesql @statement.

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

Вот простой пример:

DECLARE @statement NVARCHAR(4000)

SET @statement = N’SELECT getdate()’

EXECUTE sp_executesql @statement

Если вы запустите это в окне запроса, вы получите подобный результат:

2018-01-24 18:49:30.143

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

Ваше руководство хотело бы, чтобы это было написано как хранимая процедура. Хранимая процедура должна принимать один параметр @ReturnAverage. Если это истинно, то вы вернете среднее значение, в противном случае сумму.

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

CREATE PROCEDURE uspCalcuateSalesSummaryStatic

@returnAverage bit

IF (@returnAverage = 1)

BEGIN

SELECT SOD.ProductID,

AVG(SOD.LineTotal) as ResultAvg

FROM Sales.SalesOrderDetail SOD

INNER JOIN Sales. SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = 2011

GROUP BY SOD.ProductID

END

ELSE

BEGIN

SELECT SOD.ProductID,

SUM(SOD.LineTotal) as ResultSum

FROM Sales.SalesOrderDetail SOD

INNER JOIN Sales.SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = 2011

GROUP BY SOD.ProductID

END

Что здесь является слабым местом, так это много дублированного кода, который я выделил жирным шрифтом. Существует не так много уникального кода, но имеющийся выделен курсивом.

При всей этой избыточности у нас есть прекрасная возможность продемонстрировать некоторый динамический SQL. Давайте сделаем это!

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic

@returnAverage bit

DECLARE @statement NVARCHAR(4000),

@function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = ‘Avg’

ELSE SET @function = ‘Sum’

SET @statement =

‘SELECT SOD.ProductID,’ +

@function + + ‘(SOD.LineTotal) as Result’ + @function + ‘

FROM Sales.SalesOrderDetail SOD

INNER JOIN Sales.SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = 2011

GROUP BY SOD.ProductID’

EXECUTE sp_executesql @statement

Здесь вместо двух полных версий SQL, один для AVG, другой для SUM, мы создаем запрошенную версию «на лету».

SQL построен и сохраняется в переменной @statement. Эта переменная построена на основе значения параметра @returnAverage. Если установлено значение 1, то @function представляет Среднее; в противном случае — Суммирование.

Отладка динамического SQL

Возможно, вам интересно, как выглядит SQL во время выполнения. Вы можете легко проверить код с помощью отладчика:

Запустите хранимую процедуру с помощью команды запуска отладчика, а затем введите код.

Продолжайте до тех пор, пока вы не прочитаете инструкцию Execute, выделенную ниже.

Использование отладчика

Как только вы достигнете этой инструкции, наведите указатель мыши на @statement, и когда появится подсказка инструмента, выберите текстовый визуализатор.

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

Использование sp_executesql с параметрами

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

Инструкция принимает форму:

EXECUTE sp_executesql @statement, @parameterDefinition, @parm1=value1…, @parm2=value2, …

Итак, давайте поясним детали.

@statement — это SQL, который мы хотим выполнить.
@parameterDefinition — это строка, содержащая определение всех параметров, указанных в @statement. Перечислен каждый параметр и тип, найденный @statement. Имя и тип разделяются пробелом. Несколько параметров разделяются запятой.

Затем мы устанавливаем значения параметров, задавая параметры и желаемое значение. Параметры перечислены в порядке, определенном в строке @parameterDefinition.

@ parm1 — это первый параметр, определенный в строке @parameterDefinition. Value — это значение, которое вы хотите установить.
@ parm2 — это второй параметр, если он определен, как указано в параметре @parameterDefinition.
и так далее…

Вот простой пример, который добавляет два числа, чтобы попробовать:

DECLARE @statement NVARCHAR(4000)

DECLARE @parameterDefinition NVARCHAR(4000)

SET @statement = N’SELECT @a + @b’

SET @parameterDefinition = N’@a int, @b int’

EXECUTE sp_executesql @statement, @parameterDefinition, @a=10, @b=5

Выделены различные части инструкции:

@statement (жирный шрифт) — обратите внимание, что он включает в себя 2 параметра: @a и @b. Также обратите внимание, что они не заявлены в TSQL. Скорее, они установлены в определении параметра.
@parameterDefinition (курсив) — каждый указанный параметр определяется как тип int.

Значения параметров (жирный шрифт+курсив) — здесь мы устанавливаем значение параметра.

Для этого в этом примере у нас есть динамически исполняемый оператор SQL, который добавляет два параметра.

Эти параметры определяются как целые числа. Значение каждого параметра устанавливается в команде sp_executesql.

Пример использования sp_executesql с параметрами

Давайте рассмотрим наш предыдущий пример и расширим его. Вместо того, чтобы жестко кодировать shipDate в запросе, как мы это сделали, давайте введем это как параметр. Это делает запрос более гибким и работает с годами, кроме 2011 года.

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

Обновленная хранимая процедура с изменениями показана ниже.

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2

@returnAverage bit,

@shipDate int

DECLARE @statement NVARCHAR(4000),

@parameterDefinition NVARCHAR(4000),

@function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = ‘Avg’

ELSE SET @function = ‘Sum’

SET @parameterDefinition = ‘@shipDateYear int’

SET @statement =

‘SELECT SOD.ProductID,’ +

@function + + ‘(SOD.LineTotal) as Result’ + @function + ‘

FROM Sales. SalesOrderDetail SOD

INNER JOIN Sales.SalesOrderHEader SOH

ON SOH.SalesOrderID = SOD.SalesOrderID

WHERE YEAR(SOH.ShipDate) = @shipDateYear

GROUP BY SOD.ProductID’

EXECUTE sp_executesql @statement, @parameterDefinition, @shipDateYear=@shipDate

Чтобы запустить это, просто вызовите procpackSalesSummaryDynamic2 proc из окна запросов, используя следующую команду:

EXECUTE uspCalcuateSalesSummaryDynamic2 1,2011

Если вы это сделаете, вы увидите следующие результаты.

Результаты запроса

Позвольте мне показать вам одно прекрасное упрощение, давайте объединим @shipDateYear и @shipDate в один параметр. Мы исключим @shipDateYear из нашего кода. Это облегчит отслеживание и чтение:

CREATE PROCEDURE uspCalcuateSalesSummaryDynamic2

                @returnAverage bit,

<span>                @shipDate int

AS

DECLARE @statement NVARCHAR(4000),

       @parameterDefinition NVARCHAR(4000),

       @function NVARCHAR(10)

IF (@returnAverage = 1) SET @function = ‘Avg’

ELSE SET @function = ‘Sum’

SET @parameterDefinition = ‘@shipDate int’

SET @statement =

   ‘SELECT   SOD. ProductID,’ +

             @function + + ‘(SOD.LineTotal) as Result’ + @function + ‘

    FROM    Sales.SalesOrderDetail SOD

             INNER JOIN Sales.SalesOrderHEader SOH

                        ON SOH.SalesOrderID = SOD.SalesOrderID

    WHERE    YEAR(SOH.ShipDate) = @shipDate

    GROUP BY SOD.ProductID’

EXECUTE sp_executesql @statement, @parameterDefinition, @shipDate

Обратите внимание, что инструкция EXECUTE намного проще, нет необходимости назначать параметр инструкции SQL @shipDateYear параметру хранимой процедуры parameter @ shipDate.

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

Запуск динамического SQL с помощью EXECUTE ()

Вы также можете использовать команду EXEC или EXECUTE для запуска динамического SQL. Формат этой команды:

EXECUTE (@statement)

Вот простой пример:

DECLARE @statement NVARCHAR(4000)

SET @statement = N’SELECT getdate()’

EXECUTE (@statement)

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

Msg 2812, Level 16, State 62, Line 3

Could not find stored procedure ‘SELECT getdate()’.

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

sp_executesql против EXECUTE

Возможно, вам интересно, зачем использовать sp_executesql в сравнении с EXECUTE. Каковы различия между ними?

Вот несколько причин, по которым Microsoft рекомендует использовать sp_executesql для запуска динамического SQL:

С помощью EXECUTE все параметры могут быть преобразованы из своего исходного типа в Unicode. Это затрудняет способность оптимизатора сопоставлять динамически построенный SQL с уже существующим планом.

Используя sp_executesql, оптимизатор распознает параметры в динамическом SQL, что упрощает оптимизатор для соответствия планам.

Легче читать параметризованные запросы, чем читать кучу объединяющего их текста.

Параметрированные запросы менее подвержены атакам SQL-инъекций.

Хранимые процедуры SQL Server для начинающих
В этой статье мы узнаем, как создавать хранимые процедуры в SQL Server, на различных примерах.
Хранимая процедура SQL Server — это пакет операторов, сгруппированных в логическую единицу и хранящихся в базе данных. Хранимая процедура принимает параметры и выполняет операторы T-SQL в процедуре, возвращает набор результатов, если таковой имеется.
Чтобы понять различия между функциями и хранимыми процедурами в SQL Server, вы можете обратиться к этой статье «Функции и хранимые процедуры в SQL Server», а чтобы узнать о частичных хранимых процедурах в SQL Server, щелкните «Частичные хранимые процедуры в SQL Server».
Преимущества использования хранимой процедуры
Его можно легко изменить. : Мы можем легко изменить код внутри хранимой процедуры без необходимости перезапуска или развертывания приложения. Например, если запросы T-SQL написаны в приложении и если нам нужно изменить логику, мы должны изменить код в приложении и повторно развернуть его. Хранимые процедуры SQL Server устраняют такие проблемы, сохраняя код в базе данных. поэтому, когда мы хотим изменить логику внутри процедуры, мы можем просто сделать это с помощью простого оператора ALTER PROCEDURE.
Уменьшение сетевого трафика: Когда мы используем хранимые процедуры вместо написания запросов T-SQL на уровне приложения, по сети передается только имя процедуры, а не весь код T-SQL.
Возможность повторного использования: Хранимые процедуры могут выполняться несколькими пользователями или несколькими клиентскими приложениями без необходимости повторного написания кода.
Безопасность: Хранимые процедуры снижают угрозу, устраняя прямой доступ к таблицам.мы также можем зашифровать хранимые процедуры при их создании, чтобы исходный код внутри хранимой процедуры не был виден. Используйте сторонние инструменты, такие как ApexSQL Decrypt, для расшифровки зашифрованных хранимых процедур.
Производительность: Хранимая процедура SQL Server при первом выполнении создает план и сохраняет его в пуле буферов, чтобы этот план можно было повторно использовать при следующем выполнении.
Я создаю образцы таблиц, которые будут использоваться в примерах в этой статье.
1
2
3
4
5
6
7
8
9
10
11
12
13
140002
CREATE TABLE Product
(ProductID INT, ProductName VARCHAR (100))
GO
CREATE TABLE ProductDescription
(ProductID INT, ProductDescription VARCHAR (800))
VARCHAR
VAL
ES
(680, ‘HL Road Frame — Black, 58’)
, (706, ‘HL Road Frame — Red, 58’)
, (707, ‘Sport-100 Helmet, Red’)
GO
ВСТАВИТЬ В ОПИСАНИЕ ПРОДУКТА ЗНАЧЕНИЯ (680, ‘Сменное горное колесо для райдера начального уровня. ‘)
, (706, «Прочный сплав с быстроразъемной втулкой»)
, (707, «Аэродинамические диски для плавной езды»)
GO
Создание простой хранимой процедуры
Мы создадим простую хранимую процедуру, которая объединяет две таблицы и возвращает набор результатов, как показано в следующем примере.
СОЗДАТЬ ПРОЦЕДУРУ GetProductDesc
AS
НАЧАТЬ
УСТАНОВИТЬ NOCOUNT ON
SELECT P.ProductID, P.ProductName, PD.ProductDescription FROM
Product P
INNER JOIN ProductDescription PD ON P.ProductID = PD.ProductID
END
Мы можем использовать «EXEC ProcedureName» для выполнения хранимых процедур. Когда мы выполняем процедуру GetProductDesc, набор результатов выглядит так, как показано ниже.
Создание хранимой процедуры с параметрами
Давайте создадим хранимую процедуру SQL Server, которая принимает входные параметры и обрабатывает записи на основе входного параметра.
Ниже приведен пример хранимой процедуры, которая принимает параметр.
СОЗДАТЬ ПРОЦЕДУРУ GetProductDesc_withparameters
(@PID INT)
AS
BEGIN
SET NOCOUNT ON
SELECT P.ProductID, P.ProductDesc_withparameter 9.ProductName 9.ProductName 9.ProductName 9.ProductName 9.Product НА P.ProductID = PD.ProductID
ГДЕ P.ProductID=@PID
END
EXEC GetProductDesc_withparameters 706
При выполнении хранимой процедуры нам необходимо передать входной параметр. Пожалуйста, обратитесь к изображению ниже для получения набора результатов.
Создание хранимой процедуры со значениями параметров по умолчанию
Ниже приведен пример хранимой процедуры со значениями параметров по умолчанию.
СОЗДАТЬ ПРОЦЕДУРУ GetProductDesc_withDefaultparameters
(@PID INT = 706)
AS
BEGIN
SET NOCOUNT ON
SELECT P. ProductDescription PD ON P.ProductID = PD.ProductID
ГДЕ P.ProductID=@PID
END
Когда мы выполняем вышеуказанную процедуру без передачи значения параметра, будет использоваться значение по умолчанию 706.Но при выполнении передачи значения значение по умолчанию будет проигнорировано, а переданное значение будет рассматриваться как параметр.
Создание хранимой процедуры с выходным параметром
Ниже приведен пример хранимой процедуры с выходным параметром. В следующем примере извлекается EmpID, который является столбцом автоматической идентификации при вставке нового сотрудника.
CREATE TABLE Сотрудник (EmpID int identity (1,1), EmpName varchar (500))
СОЗДАТЬ ПРОЦЕДУРУ ins_NewEmp_with_outputparamaters
(@Ename varchar (50),
@EId int output)
AS
BEGIN
SET NOCOUNT ON
Employee
IN
ВЫБРАТЬ @ EId = SCOPE_IDENTITY ()
КОНЕЦ
Выполнение хранимых процедур с выходными параметрами немного отличается. Мы должны объявить переменную для хранения значения, возвращаемого выходным параметром.
объявить @EmpID INT
EXEC ins_NewEmp_with_outputparamaters ‘Andrew’, @EmpID OUTPUT
SELECT @EmpID
Создание зашифрованной хранимой процедуры
Мы можем скрыть исходный код в хранимой процедуре, создав процедуру с опцией «ШИФРОВАНИЕ».
Ниже приведен пример зашифрованной хранимой процедуры.
СОЗДАТЬ ПРОЦЕДУРУ GetEmployees
С ШИФРОВАНИЕМ
КАК
НАЧАТЬ
УСТАНОВИТЬ НОМЕР НА
SELECT EmpID, EmpName from Employee
END
Когда мы пытаемся просмотреть код хранимой процедуры SQL Server с помощью sp_helptext, он возвращает «Текст для объекта GetEmployees зашифрован.”
Когда вы пытаетесь создать скрипт для зашифрованной хранимой процедуры из студии управления SQL Server, выдает ошибку, как показано ниже.
Создание временной процедуры
Как и временная таблица, мы также можем создавать временные процедуры. Есть два типа временных процедур: одна — это локальная временная хранимая процедура, а другая — глобальная временная процедура.
Эти процедуры создаются в базе данных tempdb .
Локальные временные хранимые процедуры SQL Server : они создаются с префиксом # и доступны только в том сеансе, в котором они были созданы. Эта процедура автоматически отключается при закрытии соединения.
Ниже приведен пример создания локальной временной процедуры.
СОЗДАТЬ ПРОЦЕДУРУ #Temp
AS
НАЧАЛО
ПЕЧАТЬ ‘Процедура локальной температуры’
КОНЕЦ
Глобальная временная хранимая процедура SQL Server: Эти процедуры создаются с префиксом ## и могут быть доступны также в других сеансах. Эта процедура автоматически сбрасывается при закрытии соединения, которое использовалось для создания процедуры.
Ниже приведен пример создания глобальной временной процедуры.
СОЗДАТЬ ПРОЦЕДУРУ ## ТЕМП
КАК
НАЧАЛО
ПЕЧАТЬ ‘Процедура глобальной температуры’
КОНЕЦ
Изменение хранимой процедуры
Используйте оператор ALTER PROCEDURE для изменения существующей хранимой процедуры.Ниже приведен пример изменения существующей процедуры.
ИЗМЕНЕНИЕ ПРОЦЕДУРЫ GetProductDesc
AS
НАЧАЛО
УСТАНОВИТЬ НОМЕР НА
SELECT P.ProductID, P.ProductName, PD.ProductDescription FROM
PDProduct P. Код продукта
КОНЕЦ
Переименование хранимой процедуры
Чтобы переименовать хранимую процедуру с помощью T-SQL, используйте системную хранимую процедуру sp_rename. Ниже приведен пример переименования процедуры «GetProductDesc» на новое имя «GetProductDesc_new».
sp_rename ‘GetProductDesc’, ‘GetProductDesc_new’
Заключение
В этой статье мы исследовали хранимые процедуры SQL Server на различных примерах. Если у вас есть какие-либо вопросы, не стесняйтесь задавать их в разделе комментариев ниже.
SQL Server DBA, разработчик с большим опытом в администрировании, разработке, настройке производительности, мониторинге, технологиях высокой доступности и аварийного восстановления SQL Server
Последние сообщения Ранги Бабу (посмотреть все)
Учебник по хранимым процедурам SQL Server
Хранимые процедуры
SQL Server используются для группировки одной или нескольких инструкций Transact-SQL в логические единицы. Хранимая процедура хранится как именованный объект на сервере базы данных SQL Server.
При первом вызове хранимой процедуры SQL Server создает план выполнения и сохраняет его в кэше. При последующих выполнениях хранимой процедуры SQL Server повторно использует план для очень быстрого выполнения хранимой процедуры с надежной производительностью.
Эта серия руководств знакомит вас с хранимыми процедурами и показывает, как разрабатывать гибкие хранимые процедуры для оптимизации доступа к базе данных.
Раздел 1. Начало работы с хранимыми процедурами SQL Server
Базовое руководство по хранимым процедурам — покажет, как создавать, выполнять, изменять и удалять хранимые процедуры в SQL Server.
Параметры — узнайте, как создавать хранимые процедуры с параметрами, включая необязательные параметры.
Переменные — познакомит вас с переменными Transact-SQL и способами управления переменными в хранимых процедурах.
Параметры вывода — расскажет, как вернуть данные из хранимой процедуры обратно в вызывающую программу с использованием параметров вывода.
Раздел 2. Операторы управления потоком
BEGIN… END — создают блок операторов, состоящий из нескольких операторов Transact-SQL, выполняемых вместе.
IF ELSE — выполнить блок операторов на основе условия.
WHILE — многократно выполнять набор операторов на основе условия, пока условие истинно.
BREAK — немедленно выйти из цикла и пропустить оставшуюся часть кода после него внутри цикла.
ПРОДОЛЖИТЬ — немедленно пропустить текущую итерацию цикла и продолжить следующую.
Раздел 3. Курсоры
Курсор — покажет, как работать с курсорами.
Раздел 4.Обработка исключений
TRY CATCH — узнайте, как правильно обрабатывать исключения в хранимых процедурах.
RAISERROR — покажет вам, как создавать определяемые пользователем сообщения об ошибках и возвращать их обратно в приложение, используя тот же формат, что и системная ошибка.
THROW — пошаговая инструкция по созданию исключения и передаче выполнения в блок CATCH конструкции TRY CATCH .
Раздел 5. Динамический SQL
Динамический SQL — узнайте, как создавать универсальные и гибкие операторы SQL, используя технику динамического SQL.
SQL | Процедуры в PL / SQL

PL / SQL — это язык с блочной структурой, который позволяет разработчикам сочетать возможности SQL с процедурными операторами.
Хранимая процедура в PL / SQL — это не что иное, как серия декларативных операторов SQL, которые могут храниться в каталоге базы данных. Процедуру можно рассматривать как функцию или метод. Их можно вызывать через триггеры, другие процедуры или приложения на Java, PHP и т. Д.
Все операторы блока сразу передаются в механизм Oracle, что увеличивает скорость обработки и уменьшает трафик.
Преимущества:
Они приводят к повышению производительности приложения. Если процедура часто вызывается в приложении в одном соединении, то доставляется скомпилированная версия процедуры.
Они уменьшают трафик между базой данных и приложением, так как длинные операторы уже загружены в базу данных и их не нужно отправлять снова и снова через приложение.
Они добавляют к повторному использованию кода, подобно тому, как функции и методы работают в других языках, таких как C / C ++ и Java.
Недостатки:
Хранимые процедуры могут привести к значительному использованию памяти. Администратор базы данных должен определить верхнюю границу того, сколько хранимых процедур возможно для конкретного приложения.
MySQL не предоставляет функции отладки хранимых процедур.
Синтаксис для создания хранимой процедуры

УСТАНОВИТЬ ANSI_NULLS ON ИДТИ ВКЛЮЧИТЬ QUOTED_IDENTIFIER ИДТИ -- Комментарии -- СОЗДАТЬ ПРОЦЕДУРУ имя_процедуры знак равно знак равно знак равно В ВИДЕ НАЧИНАТЬ -- Запрос -- КОНЕЦ ИДТИ
Пример:
УСТАНОВИТЬ ANSI_NULLS ON ИДТИ ВКЛЮЧИТЬ QUOTED_IDENTIFIER ИДТИ СОЗДАТЬ ПРОЦЕДУРУ GetStudentDetails @StudentID int = 0 В ВИДЕ НАЧИНАТЬ УСТАНОВИТЬ NOCOUNT ON; ВЫБЕРИТЕ имя, фамилию, дату рождения, город, страну ОТ студентов ГДЕ StudentID = @ StudentID КОНЕЦ ИДТИ
Синтаксис для изменения существующей хранимой процедуры
УСТАНОВИТЬ ANSI_NULLS ON ИДТИ ВКЛЮЧИТЬ QUOTED_IDENTIFIER ИДТИ -- Комментарии -- ALTER PROCEDURE имя_процедуры знак равно знак равно знак равно В ВИДЕ НАЧИНАТЬ -- Запрос -- КОНЕЦ ИДТИ
Пример:
УСТАНОВИТЬ ANSI_NULLS ON ИДТИ ВКЛЮЧИТЬ QUOTED_IDENTIFIER ИДТИ ИЗМЕНЕНИЕ ПРОЦЕДУРЫ GetStudentDetails @StudentID int = 0 В ВИДЕ НАЧИНАТЬ УСТАНОВИТЬ NOCOUNT ON; ВЫБЕРИТЕ имя, фамилию, город ОТ студентов ГДЕ StudentID = @ StudentID КОНЕЦ ИДТИ
Синтаксис для удаления процедуры :
DROP PROCEDURE имя_процедуры
Пример:
ПРОЦЕДУРА УДАЛЕНИЯ GetStudentDetails
Автор статьи: Anannya Uberoi . Если вам нравится GeeksforGeeks, и вы хотели бы внести свой вклад, вы также можете написать статью на сайте deposit.geeksforgeeks.org или отправить свою статью по электронной почте: [email protected]. Посмотрите, как ваша статья появляется на главной странице GeeksforGeeks, и помогите другим гикам.
Пожалуйста, напишите комментарии, если вы обнаружите что-то неправильное, или если вы хотите поделиться дополнительной информацией по теме, обсуждаемой выше.
Хранимая процедура в SQL Server
В этой статье вы узнаете, как создать хранимую процедуру в SQL.Эта статья содержит ответы на следующие вопросы:
Что такое хранимая процедура в SQL?
Почему мы используем SET NOCOUNT ON в хранимой процедуре?
Сколько существует типов хранимых процедур?
Как писать комментарии в SQL Server?
Каковы соглашения об именах для хранимых процедур?
Как создать хранимую процедуру для выбора данных из вкладки базы данных с помощью запроса SELECT SQL?
Как выполнять хранимые процедуры в SQL Server?
Что такое параметры в хранимых процедурах?
Как создать параметры в хранимой процедуре запроса SELECT, которая возвращает записи в соответствии с переданным параметром?
Как создать хранимую процедуру на основе запроса INSERT?
Как создать хранимую процедуру на основе запроса UPDATE?
Как создать хранимую процедуру для удаления записей с помощью запроса DELETE?
Что такое хранимая процедура?
Хранимая процедура (SP) SQL — это набор операторов SQL и логики команд sql, которые компилируются и хранятся в базе данных. Сохраненные процедуры в SQL позволяют нам создавать SQL-запросы, которые будут храниться и выполняться на сервере. Сохраненные процедуры также можно кэшировать и использовать повторно. Основная цель хранимых процедур — скрыть прямые SQL-запросы от кода и повысить производительность таких операций с базой данных, как выбор, обновление и удаление данных.
Почему мы используем SET NOCOUNT ON в хранимой процедуре?
Пока мы устанавливаем SET NOCOUNT ON, это означает, что нет сообщений, показывающих количество затронутых строк.
NOCOUNT означает не считать, что ON.
Теперь вы узнаете, что произошло, когда SET NOCOUNT OFF.
Типы хранимых процедур
В SQL Server доступны два типа хранимых процедур:
Пользовательские хранимые процедуры
Системные хранимые процедуры
Пользовательские хранимые процедуры
Пользовательские хранимые процедуры создаются разработчиками или администраторами баз данных. Эти SP содержат еще один оператор SQL для выбора, обновления или удаления записей из таблиц базы данных. Определяемая пользователем хранимая процедура может принимать входные параметры и возвращать выходные параметры. Определяемая пользователем хранимая процедура представляет собой смесь команд DDL (язык определения данных) и DML (язык манипулирования данными).
Пользовательские SP подразделяются на два типа:
Хранимые процедуры T-SQL: T-SQL (Transact SQL) SP получают и возвращают параметры.Эти SP обрабатывают запросы Insert, Update и Delete с параметрами или без них и возвращают данные строк в качестве вывода. Это один из наиболее распространенных способов написания SP в SQL Server.
Хранимые процедуры CLR: CLR (Common Language Runtime) SP написаны на языке программирования на основе CLR, таком как C # или VB.NET, и выполняются .NET Framework.
Системные хранимые процедуры
Системные хранимые процедуры создаются и выполняются SQL Server для административных действий сервера. Разработчики обычно не вмешиваются в системные SP.
Вход в базу данных SQL Server
Давайте войдем в нашу базу данных SQL Server, чтобы мы могли достичь следующего:
Как создать хранимую процедуру на основе SELECT QUERY, которая возвращает все записи?
Как создать хранимую процедуру SELECT QUERY на основе ПАРАМЕТРОВ, которая возвращает записи на основе параметров?
Как создать хранимую процедуру на основе запроса INSERT?
Как создать хранимую процедуру на основе запроса UPDATE?
Как создать хранимую процедуру на основе запроса DELETE?
Войдите в SQL SERVER с вашим именем сервера, логином и паролем.
Переключитесь на свою базу данных. Имя моей базы данных — MBKTest.
Пустая хранимая процедура будет создана с использованием следующего:
Пустой шаблон, созданный SQL Server для SP, выглядит следующим образом. SQL-команда CREATE PROCEDURE используется для создания процедуры, за которой следует имя SP и его параметры. Области BEGIN и END используются для определения запроса операции.Здесь вы будете писать запросы на выбор, обновление, вставку или удаление.
УСТАНОВИТЬ ANSI_NULLS ON
GOER QUER 9068 904
СОЗДАТЬ ПРОЦЕДУРУ <имя_процедуры, системное имя, имя_процедуры>
<@ Param1, sysname, @ p1> = , > <@ Param2 Datatype_For_Param2`` int> =
AS
BEGIN
SET NOCOUNT ON;
SELECT <@ Param1, sysname, @ p1>, <@ Param2, sysname, @ p2>
END
GO
Как писать комментарии в SQL SERVER?
Вы можете комментировать в sql server следующими способами:
— (два дефиса / тире) для одной строки комментария.
начинаться с / * ……. заканчиваться * / для многострочных комментариев.
Каковы правила именования хранимых процедур?
Мы должны следовать стандартным соглашениям об именах, которые также могут зависеть от вашего проекта и политик кодирования.
Для соглашений об именах хранимых процедур, определяемых пользователем, я предлагаю добавить один из следующих префиксов к вашим именам SP.
sp
stp
stp_
udstp
udstp_
Соглашения об именах предназначены только для идентификации объектов.Добавляя эти префиксы в имя, мы можем четко определить, что этот объект является хранимой процедурой.
Создание таблицы базы данных
Раньше мы могли создавать и выполнять любые SP, нам нужна таблица базы данных. Я создаю таблицу базы данных с именем «tblMembers», используя следующий запрос SQL, и выполняю его на сервере. Как видите, в моей таблице 4 столбца, где первый столбец является столбцом идентификации. После создания таблицы откройте ее в SSMS и добавьте данные, вручную введя данные в таблицу.
ИСПОЛЬЗОВАТЬ [MBKTest]
GO
/ ****** Объект: Таблица [dbo]. [TblMembers] Дата сценария: 18 ноября 17, сб 18:47:55 *** *** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [TABLE d
CREATE TABLE ] [Int] IDENTITY (1,1) NOT NULL,
[MemberName] [varchar] (50) NULL,
[MemberCity] [varchar] (25) NULL,
[MemberPhone] [varchar] (15) NULL
)
GO
SET ANSI_PADDING OFF
GO
Как создать хранимую процедуру SELECT?
Щелкните свою базу данных и разверните элемент «Программируемость» и щелкните правой кнопкой мыши «Хранимые процедуры» или нажмите CTRL + N, чтобы открыть новое окно запроса. В области запроса между BEGIN и END введите оператор SELECT, чтобы выбрать записи из таблицы. См. Оператор Select в приведенном ниже коде.
УСТАНОВИТЬ ANSI_NULLS НА
GO
УСТАНОВИТЬ QUOTED_IDENTIFIER НА
GO
НАСТРОЙКА ПРОЦЕДУРЫ 9067
Выберите * из tblMembers
END
GO
Теперь нажмите F5 или кнопку «Выполнить», чтобы выполнить SP.
Вы должны увидеть сообщение «Команды успешно выполнены».
Теперь перейдите в раздел «Программируемость» -> «Хранимые процедуры», щелкните правой кнопкой мыши и выберите «Обновить».
На следующем изображении вы можете видеть, что создается новый SP с именем stpGetAllMembers.
Выполнение хранимых процедур в SQL Server
В пользовательском интерфейсе ниже щелкните правой кнопкой мыши имя SP и выберите «Выполнить хранимую процедуру». .. выполнить ИП. Отсюда вы также можете изменить существующий SP.
Кроме того, вы также можете выполнить SP из окна запроса.
Чтобы запустить хранимую процедуру в SQL Server Management Studio, переключитесь в окно запроса или CTRL + N, чтобы открыть новое окно запроса, и введите следующую команду.
Синтаксис — EXEC <имя хранимой процедуры>
Пример — EXEC stpGetAllMembers
Теперь мы запускаем нашу хранимую процедуру под названием stpGetAllMembers.Результат выглядит следующим образом:
ВЫХОД
Что такое параметры в хранимых процедурах?
Параметры в SP используются для передачи входных значений и возврата выходных значений. Есть два типа параметров:
Входные параметры — передача значений в хранимую процедуру.
Выходные параметры — возвращаемые значения из хранимой процедуры.
Как создать СП запроса SELECT с параметрами?
На предыдущих шагах мы создали простой SP, который возвращал все строки из таблицы. Теперь давайте создадим новый SP, который будет принимать название города в качестве входящего параметра и будет возвращать все строки, в которых название города соответствует значению входного параметра.
Вот обновленный SP с параметром @CityName.
УСТАНОВИТЬ ANSI_NULLS НА
GO
УСТАНОВИТЬ QUOTED_IDENTIFIER НА
GO
ВАРИАНТ СОЗДАТЬ ПРОЦЕДУРУ
УСТАНОВИТЬ NOCOUNT ON;
Выберите * From tblMembers
, где MemberCity, например ‘%’ + @ CityName + ‘%’
END
GO
Выполнить.
Чтобы запустить этот SP, введите следующую команду в инструменте запросов SQL:
EXEC GetMemberByCityName @CityName = ‘mal’
ИЛИ из пользовательского интерфейса запустите SP и введите следующие данные.
Код для выполнения выглядит следующим образом:
ИСПОЛЬЗОВАТЬ [MBKTest]
GO
DECLARE @return_value int
EXEC @return_value. [GetMemberByCityName]
@CityName = N’mal ‘
SELECT’ Возвращаемое значение ‘= @return_value
GO
OUTPUT
?
Мы можем использовать SQL-запрос INSERT INTO для вставки данных в таблицу. Следующий оператор SQL создает INSERT SP с тремя параметрами.
УСТАНОВИТЬ ANSI_NULLS НА
GO
УСТАНОВИТЬ QUOTED_IDENTIFIER НА
GO
СОЗДАТЬ ПРОЦЕДУРУ stpmber468_CARMER_CREATE PROCEDURE_stpmber468_Charma_Member_CREATE PROCEDURE_stpmber468_Charmember
@MemberPhone varchar (15)
AS
НАЧАТЬ
УСТАНОВИТЬ NOCOUNT ON;
Вставить в tblMembers (MemberName, MemberCity, MemberPhone)
Значения
(@ MemberName, @ MemberCity, @MemberPhone)
END
GO
Щелкните правой кнопкой мыши по сохраненной процедуре в обозревателе объектов и выберите «Обновить».
Передайте значение параметра в диалоговом окне «Выполнить». Примерно так:
Для выполнения этого SP в SSMS можно использовать следующий код.
ИСПОЛЬЗУЙТЕ [MBKTest]
GO
DECLARE @return_value int
EXEC @return_value = [dbo]. [StpInsertMember]
@MemberMemberName =
MemberName ‘NewYork’,
@MemberPhone = N’9999945121 ‘
SELECT’ Return Value ‘= @return_value
GO
ВЫХОД
В окне запроса вы можете проверить, есть ли новая запись для члена Имя «Махеш Чанд» добавлено в таблицу.
Вы также можете запустить тот же SP в коде.
EXEC stpInsertMember @MemberName = ‘Suhana & Ashish Kalla’, @MemberCity = ‘Mumbai’, @MemberPhone = N’
92774xxx ‘
9UTP
& Ашиш Калла »успешно добавлена.
Как создать хранимую процедуру на основе запроса UPDATE?
Давайте создадим новый SP, который будет обновлять записи таблицы на основе столбца идентификатора участника. Идентификатор передается как входной параметр. Вот новый SP, который использует команду UPDATE..SET..WHERE.
УСТАНОВИТЬ ANSI_NULLS НА
GO
УСТАНОВИТЬ QUOTED_IDENTIFIER НА
GO
СОЗДАТЬ ПРОЦЕДУРУ
СОЗДАТЬ ПРОЦЕДУРУ stpmber4 @ varIDateManager4 MemberCity varchar (25),
@MemberPhone varchar (15)
AS
НАЧАТЬ
УСТАНОВИТЬ NOCOUNT ON;
ОБНОВЛЕНИЕ tblMembers
Установить MemberName = @MemberName,
MemberCity = @MemberCity,
MemberPhone = @MemberPhone Право
Где MemberID = @MemberID
6 END
, сохраненный в процедуре GO Обозреватель объектов и выберите Обновить.Вы увидите, что SP создан.
Теперь щелкните правой кнопкой мыши имя SP и выберите «Выполнить хранимую процедуру…». Введите входные значения и выполните.
Мы можем использовать следующую команду в SSMS.
ИСПОЛЬЗУЙТЕ [MBKTest]
GO
DECLARE @return_value int
EXEC @return_value = [dbo]. [StpUpdateMemberByID] @ NallMemberByID] ,
@MemberCity = N’Mumbai ‘,
@MemberPhone = N’2541xxxx’
SELECT ‘Return Value’ = @return_value
GO
EXEC ‘Bikaner’, ‘
564xxx’
Результаты должны показать вам обновленные значения.
Как создать хранимую процедуру на основе запроса DELETE?
Давайте создадим SP, который будет удалять записи. Новый SP использует команду DELETE и удаляет все записи, соответствующие указанному идентификатору участника.
НАБОР ANSI_NULLS ПО
GO SET QUOTED_IDENTIFIER ON
GO
CREATE ПРОЦЕДУРА stpDeleteMemberByMemberID
@MemberID INT
КАК НАЧАТЬ
SET NOCOUNT ON;
Удалить из tblMembers
, где MemberId = @MemberID
END
GO
Выполнить.
Щелкните правой кнопкой мыши «Хранимые процедуры» в обозревателе объектов и выберите «Обновить».
Запустить хранимую процедуру BY UI
Теперь снова щелкните правой кнопкой мыши по хранимой процедуре и выберите «Выполнить хранимую процедуру…»
Как вы можете видеть на изображении, я пропустил значение параметра @MemberID = 4.
ВЫПОЛНИТЬ УДАЛЕНИЕ хранимой процедуры ВРУЧНУЮ (КОДИРОВКА)
EXEC stpDeleteMemberByMemberID 2
OUTPUT
Запись
успешно удалена.
В этой статье мы увидели, как создавать хранимые процедуры в базе данных SQL Server для вставки, обновления и удаления записей.
Основы хранимой процедуры в SQL Server Management Studio
Определение хранимой процедуры
Хранимая процедура — это предварительно скомпилированный набор операторов SQL и логики команд SQL, хранящихся в базе данных.
Основная цель хранимой процедуры — скрыть прямые SQL-запросы от кода и повысить производительность таких операций с базой данных, как SELECT, UPDATE и DELETE.
хранимых процедур также можно кэшировать и использовать.
Мы можем создать хранимую процедуру, используя оператор Create proc
Использование SET NOCOUNTON в хранимой процедуре
SET NOCOUNT ON — это оператор set, который предотвращает сообщение, которое показывает количество строк, затронутых операторами запроса T-SQL
NOCOUNT означает не считать, что включено.
Это означает, что при наличии SET NOCOUNT ON сообщения, которое будет показывать количество затронутых строк, не будет.
При установке NOCOUNT ON
При ВЫКЛЮЧЕНИИ НЕТ СЧЕТА
Типы хранимых процедур
В SQL Server
есть два типа хранимых процедур
Пользовательские хранимые процедуры
Системные хранимые процедуры
Пользовательские хранимые процедуры
Пользовательские хранимые процедуры создаются разработчиками баз данных или администраторами баз данных.
Эти процедуры хранения содержат один или несколько операторов SQL для записей SELECT, UPDATE или DELETE из таблиц базы данных.
Пользовательские хранимые процедуры могут принимать входные параметры и возвращать выходные параметры.
Пользовательские хранимые процедуры представляют собой смесь команд DDL (язык определения данных) и DML (язык манипулирования данными).
Пользователь определяет хранимые процедуры, которые подразделяются на два типа:
Хранимые процедуры T-SQL
Хранимые процедуры CLR
Системные хранимые процедуры
Системные хранимые процедуры
полезны при выполнении административных и информационных действий в SQL Server.Ниже описаны наиболее часто используемые системные хранимые процедуры.
для поиска всей информации об объектах базы данных
exec sp_help
Выход

Для поиска информации о таблице
exec sp_help ‘пользователь’
Выход

Найти список всех таблиц в базе данных
EXEC sys. sp_tables
Выход
Получить подробную информацию о таблицах в базе
ВЫБРАТЬ * ИЗ sys.столы
Выход
Получить определение конкретной процедуры SelectUserDetail в базе данных master.
exec sp_helptext SelectAllCustomers
Выход
Для получения сведений о зависимом объекте.
exec sp_depends Регистрация
Выход
Комментарии в хранимых процедурах
Мы можем добавлять комментарии в хранимые процедуры следующими способами:
Используйте — (два дефиса / тире) для одиночной строки комментария
Начать с / *….В конце на * /
Создать процедуру сохранения SELECT
Щелкните свою базу данных и разверните «Программируемость» и щелкните правой кнопкой мыши «Хранимые процедуры» или нажмите CTRL + N, чтобы открыть новое окно запроса.
Вы можете написать запрос SELECT между BEGIN и END, чтобы получить выбранные записи из таблицы.
Выполнить хранимую процедуру в SQL Server
Чтобы запустить хранимую процедуру на сервере SQL, выполните следующие команды
EXEC <имя процедуры сохранения>
Другой способ — щелкнуть правой кнопкой мыши имя хранимой процедуры и выбрать «Выполнить хранимую процедуру».
Параметры в хранимых процедурах
Параметры используются для передачи входных значений и возврата выходных значений в процедурах хранилища.
Входные параметры : передать значение хранимой процедуре
Выходные параметры : возвращаемые значения из хранимой процедуры
INSERT запрос в хранимой процедуре
Запрос UPDATE в хранимой процедуре
Запрос DELETE в хранимой процедуре
Использование хранимых процедур в SQL
Базы данных
SQL позволяют создавать процедуры для выполнения кода без повторного ввода логики. Хранимые процедуры — это один или несколько операторов SQL, которые выполняют некоторые действия с базой данных. Действие может быть любым: от создания таблицы до удаления данных или получения данных из ваших таблиц. По сути, хранимые процедуры — это короткие программы, созданные для вашей базы данных. Если вы извлекаете данные, возвращается набор данных. Если вы просто выполните такое действие, как INSERT, данные не будут возвращены. Любое действие SQL можно использовать в хранимой процедуре, даже вызывая другую процедуру.
Элементы хранимой процедуры
Преимущество хранимой процедуры перед написанием стандартных операторов SQL состоит в том, что ваши процедуры принимают аргументы, поэтому возвращаемые данные являются динамическими.Мы использовали статические аргументы, такие как «WHERE state = ‘tx’», где значение состояния не изменяется. Но что, если вы хотите создать программу, в которой вы выполняете поиск клиентов в состоянии, но хотите отправить SQL-оператору состояние из вашего внешнего кода? Эту задачу можно выполнить с помощью хранимой процедуры, которая принимает аргумент «Состояние».
В наших примерах используются следующие две таблицы.
Заказчик
CustomerId
Имя
Фамилия
Город
Государство
321
Франк
Loe
Даллас
Техас
455
Эд
Томпсон
Атланта
GA
456
Эд
Томпсон
Атланта
GA
457
Джо
Смит
Майами
ФЛ
458
Франк
Доу
Даллас
TX
Заказать
OrderId
CustomerId
Итого
Дата заказа
1
321
10
1/2/2014
2
455
40
02. 03.2014
3
456
20
10.03.2014
Используя приведенную выше таблицу, вы можете написать хранимую процедуру, которая находит клиентов в заданном состоянии.Элементами хранимой процедуры являются переменные, используемые в качестве аргументов и операторов внутри процедуры.
Следующий код создает процедуру, которая возвращает список клиентов на основе переменной «@state».
СОЗДАТЬ ПРОЦЕСС GetCustomersByState
(
@state varchar (2)
) КАК
ВЫБРАТЬ * ОТ клиента
ГДЕ состояние = @ состояние
Первая часть этой команды создает хранимую процедуру.Оператор CREATE PROC вам понадобится только при первом создании процедуры. После создания процедуры вы просто вызываете эту конкретную процедуру по имени, но мы немного поговорим о вызове процедуры. Если вы попытаетесь создать одну и ту же процедуру дважды, ядро базы данных SQL вернет ошибку.
Следующая часть оператора CREATE PROC — это параметры или аргументы. В этом примере хранимая процедура принимает аргумент «@state». Символ @ сообщает вам, что «@state» — это локальная переменная.Переменной присваивается тип данных varchar (2), поэтому в эту процедуру можно передать только строковое значение с двумя символами.
За оператором AS следует оператор SELECT, используемый для извлечения данных. Оператор SELECT был создан ранее для запроса клиентов в Техасе. С помощью этого оператора вы можете использовать любое состояние, переданное хранимой процедуре. После запуска процедуры отображается список клиентов.
Запуск хранимой процедуры
После создания процедуры вы можете запускать ее в любое время из любой части вашей программы.Еще одно преимущество хранимой процедуры состоит в том, что вам больше не нужно переписывать один и тот же оператор SQL. Вы просто вызываете процедуру.
Откройте механизм SQL, введите следующий код в свой редактор и запустите его.
EXEC GetCustomersByState «fl»
Приведенный выше оператор сообщает механизму SQL запустить процедуру и передает оператору «fl» в качестве аргумента. Результатом является следующий набор данных.
CustomerId
Имя
Фамилия
Город
Государство
457
Джо
Смит
Майами
FL
Процедуры могут принимать более одного аргумента.Предположим, вы хотите выполнить поиск клиентов в определенном штате и городе. Вместо добавления новой процедуры вы просто хотите изменить текущую. Как и при изменении таблицы, хранимые процедуры изменяются с помощью оператора ALTER.
Следующий оператор SQL изменяет хранимую процедуру.
ALTER PROC GetCustomersByState
(
@state varchar (2),
@ город varchar (50)
) КАК
ВЫБРАТЬ * ОТ клиента
ГДЕ штат = @ штат И город = @ город
Приведенный выше оператор добавляет параметр города, который теперь принимает значение 50 символов.Затем параметр добавляется в предложение WHERE процедуры.
Следующий код возвращает тот же набор данных, что и ранее, но процедура принимает два параметра.
EXEC GetCustomersByState «fl», «miami»
Что делать, если вы хотите использовать ту же процедуру, но иногда вы не знаете город и просто хотите пройти штат. Вы можете создать две процедуры или добавить логику в свою хранимую процедуру.
Посмотрите на следующий код процедуры.
ALTER PROC GetCustomersByState
(
@state varchar (2),
@city varchar (50) = NULL
) КАК
ЕСЛИ @city IS NULL
ВЫБРАТЬ * ОТ клиента
ГДЕ состояние = @ состояние
ELSE
ВЫБРАТЬ * ОТ клиента
ГДЕ штат = @ штат И город = @ город
В описанной выше процедуре были изменены некоторые условия.Первое изменение — это параметры. Устанавливая значение в этом разделе кода процедуры, вы присваиваете параметру или переменной значение по умолчанию. В этом коде значение по умолчанию — NULL.
Затем оператор IF использует некоторую логику для запуска кода в зависимости от значения параметра @city. Если параметр равен NULL, процедура выполняет код без переменной @city в предложении WHERE. Если он не равен NULL, процедура запускает код с переменными состояния и города.
Теперь запустите следующий оператор SQL.
EXEC GetCustomerByState «fl»
Приведенный выше оператор использует NULL для оператора и возвращает следующий набор данных.
CustomerId
Имя
Фамилия
Город
Государство
457
Джо
Смит
Майами
FL
Теперь снова запустите хранимую процедуру со следующими параметрами.
EXEC GetCustomerByState «fl», «tallahassee»
Приведенный выше оператор не возвращает записей, потому что у вас нет клиента в соответствующем городе и штате.
В процедурах используются любые операторы, если эти операторы имеют допустимый синтаксис SQL. Вы можете добавить в свою процедуру оператор JOIN, чтобы вернуть связанный набор данных между несколькими таблицами. Например, вам может понадобиться список клиентов с идентификатором заказа. Затем вы ПРИСОЕДИНЯЕТЕ свой стол клиентов к столу заказов.Используя то же имя и код хранимой процедуры, следующий оператор SQL извлечет ваши данные.
ALTER PROC GetCustomersByState
(
@state varchar (2),
@city varchar (50) = NULL
) КАК
ЕСЛИ @city IS NULL
ВЫБЕРИТЕ CustomerId, First_name, Last_Name, OrderId ОТ клиента c
Заказать JOIN o ON c.CustomerId = o.CustomerId
ГДЕ состояние = @ состояние
ELSE
ВЫБЕРИТЕ CustomerId, First_name, Last_Name, OrderId ОТ клиента
JOIN Order o ON c.CustomerId = o.CustomerId
ГДЕ штат = @ штат И город = @ город
Теперь вы можете запустить процедуру с помощью следующего кода.
EXEC GetCustomersByState «fl»
Приведенный выше код теперь возвращает следующий набор данных.
CustomerId
Имя
Фамилия
OrderId
456
Эд
Томпсон
3
456
Эд
Томпсон
3
Поскольку существует два заказа для одного и того же идентификатора клиента, возвращается несколько строк. Вы можете удалить повторяющиеся записи, добавив ключевое слово DISTINCT. Для изменения хранимой процедуры требуется другая команда ALTER, которую вы видите ниже.
ALTER PROC GetCustomersByState
(
@state varchar (2),
@city varchar (50) = NULL
) КАК
ЕСЛИ @city IS NULL
ВЫБРАТЬ DISTINCT CustomerId, First_name, Last_Name, OrderId ОТ клиента c
Заказать JOIN o ON c.CustomerId = o.CustomerId
ГДЕ состояние = @ состояние
ELSE
ВЫБЕРИТЕ DISTINCT CustomerId, First_name, Last_Name, OrderId ОТ клиента
JOIN Order o ON c.CustomerId = o.CustomerId
ГДЕ штат = @ штат И город = @ город
Удаление процедуры из базы данных
В большинстве операций с базой данных вы изменяете или добавляете процедуры. Обычно вы не удаляете процедуры, потому что, если вы не уверены, что это не повлияет на ваши программы.SQL дает вам возможность удалить процедуру, но это следует делать с осторожностью, как при удалении таблицы. После завершения процедуры вам необходимо восстановить ее из резервной копии, если вы передумали.
Ключевое слово DROP используется для удаления хранимой процедуры из вашей базы данных. Всегда проверяйте, что удаление процедуры из базы данных не повредит никаким системам. Вы можете сделать это, переименовав процедуру и тщательно протестировав среду разработки и тестирования.Ключевое слово DROP удаляет процедуру без предупреждения, поэтому вы не получаете никакой проверки перед ее запуском.
Следующий оператор SQL показывает, как удалить процедуру.
DROP PROC GetCustomeByState
Вот и все! Процедура пропала!
В этой статье рассматривается возврат записей и небольшая логика SQL внутри процедуры. Вы можете использовать операторы INSERT, UPDATE и DELETE в хранимой процедуре. Поскольку процедуры — это небольшие программы, которые влияют на критическую часть вашей системы (ваши данные), всегда тщательно проверяйте свои изменения в тестовой среде.
sql server — надежный способ проверки хранимых процедур T-SQL
Когда я столкнулся с этим вопросом, меня заинтересовал поиск безопасного, неинвазивного и быстрого метода проверки синтаксиса и ссылок на объекты (таблица, столбец).
Хотя я согласен с тем, что фактическое выполнение каждой хранимой процедуры, вероятно, вызовет больше проблем, чем просто их компиляция, следует проявлять осторожность с первым подходом. То есть вам нужно знать, что на самом деле безопасно выполнять каждую хранимую процедуру (т.е. он стирает, например, некоторые таблицы?). Эта проблема безопасности может быть решена путем обертывания выполнения в транзакции и ее отката, чтобы никакие изменения не были постоянными, как это предлагается в ответе devio. Тем не менее, этот подход потенциально может занять довольно много времени в зависимости от того, сколько данных вы обрабатываете.
Код в вопросе и первая часть ответа Олега предлагают повторно создать экземпляр каждой хранимой процедуры, поскольку это действие перекомпилирует процедуру и выполняет именно такую синтаксическую проверку. Но этот подход агрессивен — он подходит для частной тестовой системы, но может нарушить работу других разработчиков над часто используемой тестовой системой.
Я наткнулся на статью «Проверка достоверности хранимых процедур, представлений и функций SQL Server», в которой представлено решение .NET, но еще больше меня заинтриговал следующий пост от «ddblue» внизу. Этот подход получает текст каждой хранимой процедуры, преобразует ключевое слово create в alter , чтобы его можно было скомпилировать, а затем компилирует proc.И это точно сообщает о любых неверных ссылках на таблицы и столбцы. Код работает, но я быстро столкнулся с некоторыми проблемами из-за этапа преобразования create / alter.
Преобразование из «create» в «alter» ищет «CREATE» и «PROC», разделенные одним пробелом. В реальном мире могут быть пробелы или табуляции, а может быть один или несколько. Я добавил вложенную последовательность «замены» (спасибо за эту статью Джеффа Модена!), Чтобы преобразовать все такие вхождения в один пробел, что позволило продолжить преобразование, как было задумано изначально. Затем, поскольку это нужно было использовать везде, где использовалось исходное выражение «sm.definition», я добавил общее табличное выражение, чтобы избежать массового и неприглядного дублирования кода. Итак, вот моя обновленная версия кода:
ОБЪЯВИТЬ @Schema NVARCHAR (100), @ Имя NVARCHAR (100), @ Тип NVARCHAR (100), @ Определение NVARCHAR (МАКС), @CheckSQL NVARCHAR (МАКС) ОБЪЯВЛЕНИЕ КУРСОРА crRoutines ДЛЯ WITH System_CTE (имя_схемы, имя_объекта, описание_типа, тип, определение, исходное_определение) AS - Определите запрос CTE.(ВЫБРАТЬ OBJECT_SCHEMA_NAME (sm.object_id), ИМЯ ОБЪЕКТА (sm.object_id), o.type_desc, o.type, REPLACE (REPLACE (REPLACE (LTRIM (RTRIM (REPLACE (см. Определение, char (9), ''))), '', '' + CHAR (7)), CHAR (7) + '', '') , CHAR (7), '') [определение], sm.definition [orig_definition] ИЗ sys.sql_modules (NOLOCK) КАК sm ПРИСОЕДИНЯЙТЕСЬ к sys.objects (NOLOCK) КАК o ON sm.object_id = o.object_id - добавьте здесь предложение WHERE, как указано, если вы хотите протестировать подмножество перед запуском всего списка.--ГДЕ ИМЯ ОБЪЕКТА (sm.object_id) КАК 'xyz%' ) - Определите внешний запрос, ссылающийся на имя CTE. ВЫБЕРИТЕ имя_схемы, имя_объекта, type_desc, СЛУЧАЙ, КОГДА type_desc = 'SQL_STORED_PROCEDURE' THEN STUFF (определение, CHARINDEX ('CREATE PROC', определение), 11, 'ALTER PROC') КОГДА type_desc КАК '% FUNCTION%' THEN STUFF (определение, CHARINDEX ('CREATE FUNC', определение), 11, 'ALTER FUNC') КОГДА type = 'VIEW' THEN STUFF (определение; CHARINDEX ('СОЗДАТЬ ВИД'; определение); 11; ИЗМЕНИТЬ ВИД ') КОГДА type = 'SQL_TRIGGER' THEN STUFF (определение, CHARINDEX ('CREATE TRIG', определение), 11, 'ALTER TRIG') КОНЕЦ ОТ System_CTE ЗАКАЗАТЬ ПО 1, 2; ОТКРЫТЬ crRoutines ВЫБРАТЬ ДАЛЕЕ ИЗ crRoutines В @Schema, @Name, @Type, @Definition ПОКА @@ FETCH_STATUS = 0 НАЧИНАТЬ ЕСЛИ LEN (@Definition)> 0 НАЧИНАТЬ - Раскомментируйте, чтобы увидеть каждый объект отмеченным.- RAISERROR ('Проверка% s ...', 0, 1, @Name) С ТЕПЕРЬ НАЧАТЬ ПОПРОБОВАТЬ УСТАНОВИТЬ ТОЛЬКО ДЛЯ ПАРСА; EXEC (@ Определение); ВЫКЛЮЧИТЬ ТОЛЬКО ПАРСОН; КОНЕЦ ПОПЫТКИ НАЧАТЬ ЛОВ ПЕЧАТЬ @Type + ':' + @Schema + '.' + @ Имя ПЕЧАТЬ ERROR_MESSAGE () КОНЕЦ ЗАХВАТ КОНЕЦ ЕЩЕ НАЧИНАТЬ RAISERROR ('Пропуск% s ...', 0, 1, @Name) С СЕЙЧАС КОНЕЦ ВЫБРАТЬ ДАЛЕЕ ИЗ crRoutines В @Schema, @Name, @Type, @Definition КОНЕЦ ЗАКРЫТЬ crRoutines ОТКЛЮЧИТЬ crRoutines
.
No related posts.