ms sqlmanager как добавить хранимую процедуру

Работа с хранимыми процедурами

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

Объект SqlCommand имеет встроенную поддержку хранимых процедур. В частности у него определено свойство CommandType. которое в качестве значения принимает значение из перечисления System.Data.CommandType. И значение System.Data.CommandType.StoredProcedure как раз указывает, что будет использоваться хранимая процедура.

Но чтобы использовать хранимые процедуры, нам надо их вначале создать. Для этого перейдем в SQL Server Management Studio к нашей базе данных usersdb, раскроем ее узел и далее выберем Programmability->Stored Procedures. Нажмем на этот узел правой кнопкой мыши и в контекстном меню выберем пункт Stored Procedure.

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

Эта процедура выполняет добавление данных. После выражения CREATE PROCEDURE идет название процедуры. Процедура называется "sp_InsertUser", и по этому названию мы ее будем вызывать в коде C#. Название процедуры может быть любое.

Процедура имеет два входных параметра: @name и @age. Через эти параметры будут передаваться значения для имени и возраста пользователя. В самом теле процедуры после выражения AS идет стандартное sql-выражение INSERT, которое выполняет добавление данных. И в конце с помощью выражения SELECT возвращается результат. Выражение SCOPE_IDENTITY() возвращает id добавленной записи, поэтому на выходе из процедуры мы получим id новой записи. И завершается процедура ключевым словом GO.

И затем нажмем на кнопку Execute. После этого в базу данных добавляется хранимая процедура.

Подобным образом добавим еще одну процедуру, которая будет возвращать объекты:

И также для ее добавления нажмем на кнопку Execute.

Теперь перейдем к коду C# и определим следующую программу:

Для упрощения кода обращения к процедурам здесь вынесены в отдельные методы. В методе AddUser вызывается процедура sp_InsertUser. Ее название передается в конструктор объекта SqlCommand также, как и обычное sql-выражение. И с помощью выражения command.CommandType = System.Data.CommandType.StoredProcedure устанавливается, что это выражение система будет рассматривать как хранимую процедуру.

Поскольку процедура получает данные через параметры, то соответственно нам надо определить эти параметры с помощью объектов SqlParameter. Ему передается название параметра и значение. Названия параметров должны соответствовать тем названиям, которые мы определили в коде процедуры.

С помощью метода command.Parameters.Add() параметры добавляются к процедуре. И затем происходит выполнение.

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

В случае второго метода все еще проще: объекту команды просто передается название процедуры, и так как процедура фактически выполняет выражение SELECT и возвращает набор данных, то для выполнения команды мы можем использовать метод ExecuteReader(). И с помощью ридера получить все данные.

Запустим программу и введем какие-либо данные на добавление:

То что говорит SamMan я нашел, но почему то отладка эта пролетает без всякой остановки до самого конца процедуры

У меня следующие версии:

Microsoft SQL Server Management Studio 10.0.4000.0

Клиентские средства служб Microsoft Analysis Services 10.0.4000.0

Компоненты доступа к данным (MDAC) 3.86.3959

Microsoft MSXML 2.6 3.0 5.0 6.0

Microsoft Internet Explorer 8.0.6001.18702

Microsoft .NET Framework 2.0.50727.3623

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86) Sep 16 2010 20:09:22 Copyright (c) 1988-2008 Microsoft Corporation Standard Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2) (VM)

Microsoft Visual Studio 2008

Версия 9.0.30729.1 SP

Microsoft .NET Framework

Версия 3.5 SP1

Установленный выпуск: IDE Standard

Исправление для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB945282) KB945282

Это исправление предназначено для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU.

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

Для получения дополнительных сведений посетите веб-узел http://support.microsoft.com/kb/945282.

Исправление для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB946040) KB946040

Это исправление предназначено для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU.

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

Для получения дополнительных сведений посетите веб-узел http://support.microsoft.com/kb/946040.

Исправление для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB946308) KB946308

Это исправление предназначено для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU.

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

Для получения дополнительных сведений посетите веб-узел http://support.microsoft.com/kb/946308.

Исправление для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB946344) KB946344

Это исправление предназначено для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU.

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

Для получения дополнительных сведений посетите веб-узел http://support.microsoft.com/kb/946344.

Исправление для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB946581) KB946581

Это исправление предназначено для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU.

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

Для получения дополнительных сведений посетите веб-узел http://support.microsoft.com/kb/946581.

Исправление для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB947173) KB947173

Это исправление предназначено для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU.

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

Для получения дополнительных сведений посетите веб-узел http://support.microsoft.com/kb/947173.

Исправление для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB947540) KB947540

Это исправление предназначено для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU.

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

Для получения дополнительных сведений посетите веб-узел http://support.microsoft.com/kb/947540.

Исправление для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU (KB947789) KB947789

Это исправление предназначено для программы Microsoft Visual Studio 2008 Shell (integrated mode) - ENU.

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

Для получения дополнительных сведений посетите веб-узел http://support.microsoft.com/kb/947789.

Службы SQL Server Analysis Services

Конструктор служб Microsoft SQL Server Analysis Services

Версия 10.0.4000.0

Службы SQL Server Integration Services

Конструктор служб Microsoft SQL Server Integration Services

Версия 10.0.4000.0 ((Katmai_PCU_Main).100916-1939 )

Службы SQL Server Reporting Services

12. Лекция: Хранимые процедуры

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

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

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

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

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

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

Хранимые процедуры в среде MS SQL Server

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

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

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

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

Создание, изменение и удаление хранимых процедур

Создание хранимой процедуры предполагает решение следующих задач:

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

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

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

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

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

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

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

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

Ключевое слово VARYING применяется совместно с параметром OUTPUT. имеющим тип CURSOR. Оно определяет, что выходным параметром будет результирующее множество.

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

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

Параметр FOR REPLICATION востребован при репликации данных и включении создаваемой хранимой процедуры в качестве статьи в публикацию.

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

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

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

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

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

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

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

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

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

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

Пример 12.1. Процедура без параметров. Разработать процедуру для получения названий и стоимости товаров, приобретенных Ивановым.

Пример 12.1. Процедура для получения названий и стоимости товаров, приобретенных Ивановым.

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

Пример 12.2. Процедура без параметров. Создать процедуру для уменьшения цены товара первого сорта на 10%.

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

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

Пример 12.4. Процедура с входными параметрами. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

Пример 12.5. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для уменьшения цены товара заданного типа в соответствии с указанным %.

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

В этом случае уменьшается цена конфет (значение типа не указано при вызове процедуры и берется по умолчанию).

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

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

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

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

Этот блок команд позволяет определить стоимость товаров, проданных в январе ( входной параметр месяц указан равным 1).

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

Сначала разработаем процедуру для определения фирмы, где работает сотрудник.

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

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

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

ScalaHelp.RU

Apico Soft / АПИКО Софт

Спонсором блога ScalaHelp.RU является компания АПИКО Софт .

Мы предоставляем:

вторник, октября 23, 2007

MS SQL: Отладка хранимых процедур, триггеров

Сегодня не совсем скальская тема. Но в любом случае, поддержка БД MSSQL составляет существенную часть работы администратора ERP системы Scala.

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

Для запуска хранимой процедуры под отладчиком нужно запустить Query Analyzer. приконнектиться к серверу, найти в Object Browser (если это окно не открыто, то нажмите F8) нужную процедуру. Щелкните на ней правой кнопкой мыши и выберите пункт Debug. Появится окно Debug Procedure - в нем нужно заполнить значения параметров процедуры, с которыми вы хотите ее запустить. После нажатия кнопки Execute вы попадет в отладчик. Возможности отладчика стандартные: пошаговое выполнение, проверка значений переменных, точки останова. Думаю тут ничего сложного.

С хранимыми процедурами понятно. А как быть, если нужно отладить, например, триггер? В этом случае есть одна хитрость - нужно сначала сделать хранимую процедуру, действия в которой приведут к выполнению триггера. После этого запускаем отладку этой вспомогательной процедуры, доходим пошагово до строки, зажигающей триггер, и жмем на ней кнопку Step Into (F11). В результате должен открыться текст триггера, а выполнение его остановится на первой строке - то, что нам и было нужно. Я думаю, что такой же фокус пройдет и с функцией, каюсь, сам не пробовал.

В MS SQL 2005 функциональность отладки была существенно переработана. Теперь для отладки нужно использовать Visual Studio. Причем версии Express Edition и Standard для этого не подойдут, только Professional и Team. Правда, все стало проще - в окне Server Explorer щелкаем правой кнопкой мыши на любом объекте БД (хранимой процедуре, триггере, функции), выбираем из локального меню пункт Step Into… и попадаем в отладку. Причем, отлаживать можно как хранимые процедуры, написанные на T-SQL, так и на C# или VB.NET.

Попробовал воспользоваться для отладки процедуры на 2005м сервере Query Analyzer из 2000го - ругается [SQL-DMO]You must use SQL Server 2005 management tools to connect to this server .

UPDATE: Ситуация с отладчиком улучшилась с выходом MS SQL 2008. Теперь запускать отладку можно непосредственно из MS SQL Server Management Studio. Просто в окне редактирования запускаем скрипт через ALT+F5. Доступны стандартные методы отладки.