Возврат данных из хранимой процедуры

Windows 2008 — 2012 R2

Чтобы удалить SQL Server из Windows Server 2008, Windows Server 2012 и Windows 2012 R2, выполните следующие действия:

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

2. Щелкните  правой кнопкой мыши Microsoft SQL Server (Версия) (Bit) и   выберите Uninstall. Например, SQL Server 2012 (64-bit).

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

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

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

6. На странице Готов к удалению просмотрите список компонентов и функций, которые   будут неустановлены. Щелкните Удалить,   чтобы приступить к удалению.

7.  Обновите окно «Программы и функции», чтобы проверить успешное удаление экземпляра SQL Server и определить, какие компоненты SQL Server все еще   существуют. Удалите эти компоненты из этого окна, если вы решите.

Системные хранимые процедуры для работы с API

Пользователи, запускающие приложение Приложение SQL Server Profiler для приложений ADO, OLE DB и ODBC, могут заметить, что эти приложения используют системные хранимые процедуры, не описанные в справочнике по Transact-SQL. Эти хранимые процедуры используются Microsoft SQL Server поставщиком собственного клиента OLE DB и SQL Server драйвером ODBC собственного клиента для реализации функций API базы данных. Эти хранимые процедуры — всего лишь механизм, задействованный поставщиком или драйвером для передачи запросов пользователя экземпляру SQL Server. Они предназначены только для внутреннего использования поставщиком или драйвером. Явное обращение из SQL Server приложения на основе не поддерживается.

Хранимые процедуры sp_createorphan и sp_droporphans используются для обработки в ODBC ntext, Text и Image .

Хранимая процедура sp_reset_connection используется SQL Server для поддержки вызовов в транзакциях удаленных хранимых процедур. Кроме того, эта хранимая процедура инициирует события Audit Login и Audit Logout при повторном использовании соединения из пула соединений.

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

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

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

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

Настройте распространение с помощью мастера создания публикаций или мастера настройки распространителя. После настройки распространителя просмотрите и измените его свойства в диалоговом окне Свойства распространителя — <Distributor> . Используйте мастер настройки распространителя, если необходимо настроить распространитель так, чтобы члены предопределенных ролей базы данных могли создавать публикации, или если необходимо настроить удаленный распространитель, который не является издателем.

Настройка распространения

1. В среде Microsoft SQL Server Management Studio подключитесь к серверу, который будет выступать в роли распространителя (в большинстве случаев издатель и распространитель являются одним сервером), и разверните узел сервера.

2. Щелкните правой кнопкой мыши папку Репликация , затем щелкните Настройка распространения.

3. Выполняйте инструкции мастера настройки распространителя:

• Выберите распространитель. Чтобы использовать локальный распространитель, выберите имя ServerName, которое будет выступать в качестве своего собственного распространителя. При этом SQL Server создаст базу данных распространения и журнал. Для использования удаленного распространителя выберите Использовать следующий сервер в качестве распространителя, а затем выберите сервер. Сервер должен быть сконфигурирован в качестве распространителя, а издатель должен быть включен для использования распространителя. Дополнительные сведения см. в разделе Включение удаленного издателя на распространителе (среда SQL Server Management Studio).

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

• Укажите корневую папку моментальных снимков (для локального распространителя). Папка моментальных снимков — это просто каталог, назначенный для совместного использования; агенты, считывающие и записывающие данные в эту папку, должны иметь достаточные разрешения для доступа к ней. Каждый издатель, использующий распространитель, создает папку в корневой папке, каждая публикация создает папки в папке «Издатель», в которой хранятся файлы моментальных снимков. Дополнительные сведения о надлежащей защите папок см. в разделе Организация безопасности папки моментальных снимков.

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

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

• При необходимости создайте скрипт настроек конфигурации. Дополнительные сведения см. в разделе Scripting Replication.

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

Ограничения

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

Чтобы показать точные имена системных процедур, запросите представления каталога sys.system_objects и sys.system_parameters .

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

Рекомендации

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

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

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

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

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

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

в статье Указание параметров.

-Или-

Если не указано уточненное имя определяемой пользователем процедуры, компонент Компонент Database Engine производит поиск процедуры в следующем порядке.

1. Схема текущей базы данных.

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

3. Схема в текущей базе данных.

Автоматическое выполнение хранимых процедур

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

Автоматически выполняемая процедура работает с теми же разрешениями, что и члены предопределенной роли сервера sysadmin . Любое сообщение об ошибке, сформированное такой процедурой, записывается в журнал ошибок SQL Server .

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

Совет

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

Установка, отмена и управление автоматическим выполнением

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

Используйте процедуру sp_procoption чтобы:

1. обозначить существующую процедуру как автоматически запускаемую;

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

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

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

Можно использовать один из следующих способов:

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

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

1. В обозревателе объектов подключитесь к экземпляру Компонент Database Engine и разверните его.

2. Последовательно разверните узел Базы данных, базу данных, которой принадлежит процедура, и узел Программирование.

3. Разверните раздел Хранимые процедуры, щелкните процедуру правой кнопкой мыши, нажмите Создать скрипт для хранимой процедуры, а затем выберите один из следующих пунктов: Используя CREATE, Используя ALTER или Используя DROP и CREATE.

4. Выберите New Query Editor Window (Окно редактирования нового запроса). При этом отобразится определение процедуры.

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

Системная хранимая процедура: sp_helptext

1. В обозревателе объектов установите соединение с экземпляром компонента Компонент Database Engine.

2. На панели инструментов нажмите Создать запрос.

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

   

   

   

   

   

   

   

   

   

   

Системная функция: OBJECT_DEFINITION

1. В обозревателе объектов установите соединение с экземпляром компонента Компонент Database Engine.

2. На панели инструментов нажмите Создать запрос.

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

Что такое хранимые процедуры в T-SQL?

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

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

В хранимых процедурах, в отличие от функций, уже можно выполнять операции модификации данных такие как: INSERT, UPDATE, DELETE. Также в процедурах можно использовать SQL инструкции практически любого типа, например, CREATE TABLE для создания таблиц или EXECUTE, т.е. вызов других процедур. Исключение составляет несколько типов инструкций таких как: создание или изменение функций, представлений, триггеров, создание схем и еще несколько других подобных инструкций, например, также нельзя в хранимой процедуре переключать контекст подключения к базе данных (USE).

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

Хранимые процедуры очень полезны, они помогают нам автоматизировать или упростить многие операции, например, Вам постоянно требуется формировать различные сложные аналитические отчеты с использованием сводных таблиц, т.е. оператора PIVOT. Чтобы упростить формирование запросов с этим оператором (как Вы знаете, у PIVOT синтаксис достаточно сложен), Вы можете написать процедуру, которая будет Вам динамически формировать сводные отчеты, например, в материале «Динамический PIVOT в T-SQL» представлен пример реализации данной возможности в виде хранимой процедуры.

Основные понятия отладки SQL Server

Microsoft SQL Server 2005 была разработана для обеспечения интеграции со средой CLR, котораяиспользуется всеми сборками .NET. Следовательно, SQL Server 2005 поддерживает управляемые объекты базы данных. Таким образом, можно создавать объекты базы данных, такие как хранимые процедуры и определяемые пользователем функции (UDF), C# в качестве методов в классе. Это позволяет этим хранимым процедурам и функциям UDF использовать функции в .NET Framework и из собственных пользовательских классов. Конечно, SQL Server 2005 также обеспечивает поддержку объектов базы данных T-SQL.

SQL Server 2005 предлагает поддержку отладки как для T-SQL, так и для управляемых объектов базы данных. Однако эти объекты можно отлаживать только с помощью выпусков Visual Studio 2005 Professional и Team Systems. В этом учебнике мы рассмотрим отладку объектов базы данных T-SQL. В следующем учебнике рассматривается отладка управляемых объектов базы данных.

Общие сведения об отладке T-SQL и CLR в записи блога SQL Server 2005 из группы интеграции SQL Server 2005 CLR выделяют три способа отладки объектов SQL Server 2005 из Visual Studio:

Прямая отладка базы данных (DDD) — начиная с обозреватель сервера можно выполнить шаг с заходом в любой объект базы данных T-SQL, например хранимые процедуры и UDF. Мы рассмотрим DDD на шаге 1.

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

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

Отладка приложения проверяется, начиная с шага 2.

Отладка SQL Server выпусков проекта Visual Studio Professional и Team Systems включает тип проекта SQL Server, который обычно используется для создания управляемых объектов базы данных. В следующем руководстве мы рассмотрим использование проектов SQL Server и отладку их содержимого.

Visual Studio может выполнять отладку хранимых процедур на локальном и удаленном экземплярах SQL Server. Локальный экземпляр SQL Server — это тот, который устанавливается на том же компьютере, что и Visual Studio. Если используемая база данных SQL Server не находится на компьютере разработки, она считается удаленным экземпляром. Для этих руководств мы использовали локальные экземпляры SQL Server. Отладка хранимых процедур на удаленном экземпляре SQL Server требует больше этапов настройки, чем при отладке хранимых процедур на локальном экземпляре.

Если вы используете локальный экземпляр SQL Server, можно начать с шага 1 и выполнить действия, описанные в этом руководстве, до конца. Однако если вы используете удаленный экземпляр SQL Server, то сначала необходимо убедиться, что при отладке на компьютере разработки будет использоваться учетная запись пользователя Windows с SQL Server именем входа на удаленном экземпляре. Кроме того, имя входа базы данных и имя входа базы данных, используемые для подключения к базе данных из работающего приложения ASP.NET, должны быть членами роли . Дополнительные сведения о настройке Visual Studio и SQL Server для отладки удаленного экземпляра см. в разделе Отладка объектов базы данных T-SQL в удаленных экземплярах в конце этого руководства.

Наконец, следует понимать, что поддержка отладки для объектов базы данных T-SQL не так обширна, как поддержка отладки для приложений .NET. Например, условия точки останова и фильтры не поддерживаются, доступно только подмножество окон отладки, нельзя использовать «изменить и продолжить», окно интерпретации подготавливается к просмотру бесполезным и т. д. Дополнительные сведения см. в разделе ограничения на команды и функции отладчика .

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

Можно использовать один из следующих способов:

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

Создание процедуры в обозревателе объектов

1. В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine и разверните его.

2. Последовательно разверните узел Базы данных, базу данных AdventureWorks2012 и узел Программирование.

3. Щелкните правой кнопкой мыши элемент Хранимые процедуры и выберите пункт Создать хранимую процедуру.

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

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

   Параметр	Значение
   Автор	Ваше имя
   Дата создания	Сегодняшняя дата
   Описание	Возвращает данные о сотрудниках.
   Procedure_name	HumanResources.uspGetEmployeesTest
   @Param1	@LastName
   @Datatype_For_Param1	nvarchar(50)
   Default_Value_For_Param1	NULL
   @Param2	@FirstName
   @Datatype_For_Param2	nvarchar(50)
   Default_Value_For_Param2	NULL

6. Нажмите кнопку ОК.

7. В редакторе запросов замените инструкцию SELECT следующей инструкцией:

8. Для проверки синтаксиса выберите пункт Выполнить анализ в меню Запрос. Если возвращается сообщение об ошибке, сравните инструкции с приведенными выше и при необходимости внесите исправления.

9. Чтобы создать процедуру, в меню Запрос выберите пункт Выполнить. Процедура создается как объект в базе данных.

   

   

   

   

   

10. Чтобы увидеть процедуру в обозревателе объектов, щелкните правой кнопкой мыши элемент Хранимые процедуры и выберите пункт Обновить.

11. Чтобы выполнить процедуру, в обозревателе объектов щелкните правой кнопкой мыши имя хранимой процедуры HumanResources.uspGetEmployeesTest и выберите пункт Выполнение хранимой процедуры.

12. В окне Выполнение процедуры введите Margheim в качестве значения для параметра @LastName и Diane в качестве значения для параметра @FirstName.

Предупреждение

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

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

Создание процедуры в редакторе запросов

1. В обозревателе объектов подключитесь к экземпляру компонента Компонент Database Engine.

2. В меню Файл выберите пункт Создать запрос.

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

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

Использование расширенных событий

Для трассировки выполнения запроса используйте расширенное событие sp_statement_completed . Создайте сеанс с этим событием, при этом можно использовать фильтр в object_id для определенной хранимой процедуры, скомпилированной в собственном коде. Расширенное событие вызывается после выполнения каждого запроса. Время ЦП и время существования, указанные расширенным событием, показывают объем ресурсов ЦП, который потребовался на выполнение запроса, и время его выполнения. Скомпилированная в собственном коде хранимая процедура, которая потребляет значительное время ЦП, может сталкиваться с проблемами производительности.

line_number вместе с object_id в расширенном событии можно использовать для анализа запросов. Следующий запрос может использоваться для получения определения процедуры. Номер строки можно использовать для поиска запроса в определении.

Причина

Эта проблема возникает из-за того, что по умолчанию в SQL Server Management Studio включена опция предотвращения сохранения, требуемая параметра   повторного создания таблицы.

При изменении таблицы, чтобы изменить структуру метаданных таблицы, а затем сохранить таблицу, таблица должна быть повторно создана на основе этих изменений. Это может привести к потере метаданных и непосредственной потере данных при повторном создании таблицы. Если включить функцию Предотвращение сохранения изменений, которые требуют параметра повторного создания таблицы в разделе Конструктор в окне Параметры SQL Server Management Studio (SSMS), вы получите сообщение об ошибке, упомянутое в разделе «Симптомы».