Что нужно знать для отладки хранимых процедур T-SQL

Для выполнения отладки хранимой процедуры на T-SQL в SQL Server 2000 используется Query Analyzer. Нужно просто щелкнуть правой кнопкой мыши на хранимой процедуре и выбрать Debug. В SQL Server 2005 среда SQL Server Management Studio заменила Query Analyzer. Логично предположить, что для того, чтобы выполнить отладку хранимой процедуры на T-SQL в SQL Server 2005, достаточно просто щелкнуть правой кнопкой мыши на хранимой процедуре и выбрать функцию отладки в Management Studio. К сожалению, не так все просто. Где искать функцию отладки — неизвестно.

Чтобы отладить хранимую процедуру T-SQL в SQL Server 2005, нужно задействовать окружение разработки Visual Studio 2005. Однако, прежде чем я покажу, как это сделать, потребуется создать хранимую процедуру, чтобы было что отлаживать. Вместо использования существующей хранимой процедуры или создания ее в Management Studio давайте воспользуемся не столь известным типом проекта Visual Studio 2005 под названием Database и создадим хранимую процедуру на T-SQL. Находясь в проектах Database, можно обратиться к проектировщику запросов и представлений, Query and View Designer, графическому инструментальному средству, которое можно использовать для создания хранимых процедур на T-SQL.

Создание хранимой процедуры T-SQL через графический интерфейс

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

В Visual Studio 2005 нужно создать новый проект, выбрав в меню File. В качестве альтернативы можно использовать набор клавиш Ctrl+Alt+N. В диалоговом окне следует раскрыть узел Other Project Types дерева в левой панели экрана и указать Database. Далее в правой панели с заголовком Visual Studio installed templates нужно выбрать Database Project, после чего требуется дать проекту имя и указать местоположение, затем щелкнуть OK.

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

Экран 1. Диалоговое окно создания нового подключения

После того как будет выбрано соединение (или создано новое), создаем в новом приложении новый проект Database. Приложение есть самый крупный элемент управления в среде разработки Visual Studio 2005. Этот элемент может содержать один или более проектов с одним или более файлами текстов кода.

В Visual Studio 2005 в Explorer имеется два окна. Справа появляется окно Solution Explorer, оно позволяет просматривать проекты и файлы внутри приложения. Слева выводится Server Explorer, в нем дается информация о соединениях базы данных и системная информация, имеющая отношение к серверам сети. Если эти окна скрыты, можно сделать их видимыми из меню View. В качестве альтернативы используются сочетания клавиш Ctrl+Alt+L (для Solution Explorer) и Ctrl+Alt+S (для Server Explorer).

Чтобы создать хранимую процедуру T-SQL, нужно выбрать в меню Project пункт Add New Item (или использовать набор клавиш Ctrl+Shift+A). В диалоговом окне Add New Item, которое представлено на экране 2, можно выбрать различные элементы, включающие шаблоны для создания хранимых процедур, сценарии триггеров и сценарии для создания таблиц. Эти шаблоны можно переделывать и даже добавлять собственные.

Создадим для отладки хранимую процедуру T-SQL. Выберем шаблон Stored Procedure Script, дадим ему название и щелкнем OK. Получим файл с шаблоном сценария, который содержит некоторую структуру кода для создания хранимой процедуры. Шаблон включает в себя код для удаления существующей хранимой процедуры, создания хранимой процедуры и предоставления разрешения на исполнение. Остается только написать код, который должен будет выполняться в теле хранимой процедуры. И даже можно получить подсказку по выполнению этой задачи. Если щелкнуть правой кнопкой мыши на файле сценария, появится контекстное меню, в котором есть пункт Insert SQL. Если выбрать этот пункт, появится Query and View Designer (по-другому Query Builder).

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

Query Builder состоит из четырех накладывающихся друг на друга вертикальных панелей.

  • Панель диаграмм. Вверху панели Diagram показаны запрашиваемые таблицы, представления и функции.
  • Панель ключевых условий. Панель Criteria содержит электронную таблицу с сеткой, в которой можно определять столбцы для представления, критерии выбора и другие критерии.
  • Панель SQL. Панель SQL содержит текстовый редактор, который отображает операторы SQL, созданные в Query Builder. Их можно редактировать и добавлять собственные.
  • Панель вывода. Вверху панели Results отображаются результаты работы запросов.
Экран 3. Диалоговое окно Add Table в Query Builder

При запуске Query Builder также открывается диалоговое окно Add Table, которое изображено на экране 3. В этом диалоговом окне выбираются таблицы, представления и функции, необходимые для работы. Как показано на экране, я выбрал четыре таблицы. Щелчком на кнопке Add добавляем таблицы в панель Diagram. Как показано на экране 4, каждая таблица получает собственный столбец, который содержит флажки для каждого столбца. Со временем на панели Diagram появятся взаимосвязи между таблицами. Как видно из экрана 4, связи представляются в виде линий между таблицами.

Экран 4. Взаимосвязи между таблицами на панели Diagram

На панели SQL Query Builder создает выражение FROM, основанное на связях, построенных между таблицами. Можно выбирать поля, указывая флажки в различных таблицах на панели диаграмм или выбирая название поля из раскрывающегося списка на панели. Например, на экране 4 я выбрал таблицу ProductModel и для нее из раскрывающегося списка на панели Criteria поле Name.

На панели Criteria и на панели SQL выбранным полям с одинаковыми именами даются псевдонимы. Названия этих псевдонимов по умолчанию: Expr1, Expr2 и т. д. В одной из двух панелей, на панели Criteria или на панели SQL, можно давать псевдонимам интуитивно понятные названия.

В панели SQL, если в этом есть необходимость, можно изменять предложения SQL, созданные Query Builder. Когда текст запроса удовлетворяет, можно проверить его результаты, щелкнув на кнопке Execute Query. Если результаты приемлемы, можно нажать OK и вставить предложение SQL в файл сценария. Далее можно продолжать редактировать текст кода внутри самого файла.

Теперь, когда с хранимой процедурой все ясно, пришло время создать ее в SQL Server 2005. Читатели, которым приходилось создавать приложения в Visual Basic .NET или в Visual C# .NET, знают, что для того, чтобы создать приложение, используется меню Build. В проекте Database нет меню Build. Вместо этого надо сохранить файл сценария, затем выбрать Run из меню Project. Чтобы увидеть, что происходило, можно открыть окно Output в меню View (или использовать набор клавиш Ctrl+Alt+O).

Если в окне Output ошибок нет, значит, хранимая процедура T-SQL создана успешно. Для перепроверки следует открыть Server Explorer и Data Connections, затем используемое соединение, далее контейнер Stored Procedures. Созданная хранимая процедура должна быть в списке. Если ее нет, следует щелкнуть правой кнопкой мыши на узле Stored Procedures и выбрать Refresh.

Как отладить хранимую процедуру T-SQL

Как я только что продемонстрировал, для создания хранимой процедуры на T-SQL можно использовать проект Database в Visual Studio 2005. Однако не нужно отлаживать эту хранимую процедуру внутри проекта Database. Отладку можно выполнить в Server Explorer.

Откройте Server Explorer и раскройте контейнер Data Connections. Если есть контейнер соединения для базы данных, в которой требуется отладить хранимую процедуру T-SQL, следует раскрыть этот контейнер соединения. Если такого контейнера соединения нет, нужно щелкнуть правой кнопкой мыши на Database Connections и выбрать Add Connection. Следуя инструкциям, которые я приводил выше, и используя диалоговое окно, показанное на экране 1, добавим соединение.

После того как контейнер появился, надо раскрыть его. Затем следует раскрыть контейнер Stored Procedures и выбрать хранимую процедуру, которую требуется отладить. Для запуска процесса отладки нужно сделать так же, как в Query Analyzer в SQL Server 2000: щелкнуть правой кнопкой мыши на хранимой процедуре, затем выбрать пункт Step Into Stored Procedure. Если процедура получает параметры, появится диалоговое окно Run Stored Procedure. В этом диалоговом окне вводятся значения параметров процедуры. Задав значения параметров, нужно щелкнуть OK, и текст кода хранимой процедуры загрузится в среду разработки. Если процедура не принимает параметры, текст хранимой процедуры загрузится сразу. Как показано на экране 5, в диалоговом окне видно, что выполнение остановлено на первой строке кода процедуры.

Экран 5. Начало отладки хранимой процедуры

Внизу диалогового окна отладки можно увидеть окна Call Stack и Autos. В окне Call Stack виден исполняемый код. Окно Autos отображает переменные, используемые в текущем предложении, три предложения, предшествующие текущему предложению, и три предложения после текущего предложения. Окно Autos входит в группу окон, называемых окнами переменных. Что эти окна имеют общего? То, что они способны отображать, раскрывать и редактировать переменные и выражения. Кроме окна Autos есть другие окна группы: окно Locals отображает локальные переменные в текущем контексте или диапазоне, а окно Watch позволяет добавлять переменные, чьи значения надо увидеть.

Для пошагового исполнения одной строки кода следует нажать F10. На каждом шаге можно инспектировать значения переменных и другие элементы кода, проводя по ним мышкой или используя окна Autos, Locals и Watch. Если хранимая процедура возвращает результирующий набор, его можно увидеть в окне Output после того, как будет пройден весь код.

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

Как можно было заметить, проект Database в Visual Studio 2005 снабжен удобными графическими инструментальными средствами для создания хранимых процедур T-SQL. Visual Studio 2005 также позволяет легко проводить отладку хранимых процедур T-SQL. Надо только знать, как найти функцию отладки.

Нильс Берглунд - Сотрудник исследовательского подразделения DevelopMentor, специализируется на программном обеспечении .NET и базах данных. Соавтор курсов по базам данных в DevelopMentor и книги A First Look at SQL Server 2005 for Developers. nielsb@develop.com