Sql server declare error - Исправление ошибок и поиск оптимальных решений проблем

Время прочтения
16 мин

Просмотры 35K

Привет, Хабр! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.

1. Введение

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

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

Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.

1.1 Зачем нужна обработка ошибок?

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

Мы часто хотим, чтобы в базе данных изменения были атомарными. Например, задача по переводу денег с одного счета на другой. С этой целью мы должны изменить две записи в таблице CashHoldings и добавить две записи в таблицу Transactions. Абсолютно недопустимо, чтобы ошибки или сбой привели к тому, что деньги будут переведены на счет получателя, а со счета отправителя они не будут списаны. По этой причине обработка ошибок также касается и обработки транзакций. В приведенном примере нам нужно обернуть операцию в BEGIN TRANSACTION и COMMIT TRANSACTION, но не только это: в случае ошибки мы должны убедиться, что транзакция откачена.

2. Основные команды

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

2.1 TRY-CATCH

Основным механизмом обработки ошибок является конструкция TRY-CATCH, очень напоминающая подобные конструкции в других языках. Структура такова:

BEGIN TRY
   <обычный код>
END TRY
BEGIN CATCH
   <обработка ошибок>
END CATCH

Если какая-либо ошибка появится в <обычный код>, выполнение будет переведено в блок CATCH, и будет выполнен код обработки ошибок.

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

Вот очень быстрый пример:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

Результат выполнения: This is the error: Divide by zero error encountered.

Мы вернемся к функции error_message() позднее. Стоит отметить, что использование PRINT в обработчике CATCH приводится только в рамках экспериментов и не следует делать так в коде реального приложения.

Если <обычный код> вызывает хранимую процедуру или запускает триггеры, то любая ошибка, которая в них возникнет, передаст выполнение в блок CATCH. Если более точно, то, когда возникает ошибка, SQL Server раскручивает стек до тех пор, пока не найдёт обработчик CATCH. И если такого обработчика нет, SQL Server отправляет сообщение об ошибке напрямую клиенту.

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

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

Выходные данные:

This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'

Как можно видеть, блок TRY присутствует, но при возникновении ошибки выполнение не передается блоку CATCH, как это ожидалось. Это применимо ко всем ошибкам компиляции, таким как пропуск колонок, некорректные псевдонимы и тому подобное, которые возникают во время выполнения. (Ошибки компиляции могут возникнуть в SQL Server во время выполнения из-за отложенного разрешения имен – особенность, благодаря которой SQL Server позволяет создать процедуру, которая обращается к несуществующим таблицам.)

Эти ошибки не являются полностью неуловимыми; вы не можете поймать их в области, в которой они возникают, но вы можете поймать их во внешней области. Добавим такой код к предыдущему примеру:

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Теперь мы получим на выходе это:

This prints
The error message is: Invalid object name 'NoSuchTable'.

На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.

2.2 SET XACT_ABORT ON

В начало ваших хранимых процедур следует всегда добавлять это выражение:

SET XACT_ABORT, NOCOUNT ON

Оно активирует два параметра сессии, которые выключены по умолчанию в целях совместимости с предыдущими версиями, но опыт доказывает, что лучший подход – это иметь эти параметры всегда включенными. Поведение SQL Server по умолчанию в той ситуации, когда не используется TRY-CATCH, заключается в том, что некоторые ошибки прерывают выполнение и откатывают любые открытые транзакции, в то время как с другими ошибками выполнение последующих инструкций продолжается. Когда вы включаете XACT_ABORT ON, почти все ошибки начинают вызывать одинаковый эффект: любая открытая транзакция откатывается, и выполнение кода прерывается. Есть несколько исключений, среди которых наиболее заметным является выражение RAISERROR.

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

Для надежной обработки ошибок в SQL Server вам необходимы как TRY-CATCH, так и SET XACT_ABORT ON. Среди них инструкция SET XACT_ABORT ON наиболее важна. Если для кода на промышленной среде только на нее полагаться не стоит, то для быстрых и простых решений она вполне подходит.

Параметр NOCOUNT не имеет к обработке ошибок никакого отношения, но включение его в код является хорошей практикой. NOCOUNT подавляет сообщения вида (1 row(s) affected), которые вы можете видеть в панели Message в SQL Server Management Studio. В то время как эти сообщения могут быть полезны при работе c SSMS, они могут негативно повлиять на производительность в приложении, так как увеличивают сетевой трафик. Сообщение о количестве строк также может привести к ошибке в плохо написанных клиентских приложениях, которые могут подумать, что это данные, которые вернул запрос.

Выше я использовал синтаксис, который немного необычен. Большинство людей написали бы два отдельных выражения:

SET NOCOUNT ON
SET XACT_ABORT ON

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

3. Основной пример обработки ошибок

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

Для примера я буду использовать эту простую таблицу.

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

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

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

Первая строка в процедуре включает XACT_ABORT и NOCOUNT в одном выражении, как я показывал выше. Эта строка – единственная перед BEGIN TRY. Все остальное в процедуре должно располагаться после BEGIN TRY: объявление переменных, создание временных таблиц, табличных переменных, всё. Даже если у вас есть другие SET-команды в процедуре (хотя причины для этого встречаются редко), они должны идти после BEGIN TRY.

Причина, по которой я предпочитаю указывать SET XACT_ABORT и NOCOUNT перед BEGIN TRY, заключается в том, что я рассматриваю это как одну строку шума: она всегда должна быть там, но я не хочу, чтобы это мешало взгляду. Конечно же, это дело вкуса, и если вы предпочитаете ставить SET-команды после BEGIN TRY, ничего страшного. Важно то, что вам не следует ставить что-либо другое перед BEGIN TRY.

Часть между BEGIN TRY и END TRY является основной составляющей процедуры. Поскольку я хотел использовать транзакцию, определенную пользователем, я ввел довольно надуманное бизнес-правило, в котором говорится, что если вы вставляете пару, то обратная пара также должна быть вставлена. Два выражения INSERT находятся внутри BEGIN и COMMIT TRANSACTION. Во многих случаях у вас будет много строк кода между BEGIN TRY и BEGIN TRANSACTION. Иногда у вас также будет код между COMMIT TRANSACTION и END TRY, хотя обычно это только финальный SELECT, возвращающий данные или присваивающий значения выходным параметрам. Если ваша процедура не выполняет каких-либо изменений или имеет только одно выражение INSERT/UPDATE/DELETE/MERGE, то обычно вам вообще не нужно явно указывать транзакцию.

В то время как блок TRY будет выглядеть по-разному от процедуры к процедуре, блок CATCH должен быть более или менее результатом копирования и вставки. То есть вы делаете что-то короткое и простое и затем используете повсюду, не особо задумываясь. Обработчик CATCH, приведенный выше, выполняет три действия:

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

Эти три действия должны всегда быть там. Мы можете возразить, что строка

IF @@trancount > 0 ROLLBACK TRANSACTION

не нужна, если нет явной транзакции в процедуре, но это абсолютно неверно. Возможно, вы вызываете хранимую процедуру, которая открывает транзакцию, но которая не может ее откатить из-за ограничений TRY-CATCH. Возможно, вы или кто-то другой добавите явную транзакцию через два года. Вспомните ли вы тогда о том, что нужно добавить строку с откатом? Не рассчитывайте на это. Я также слышу читателей, которые возражают, что если тот, кто вызывает процедуру, открыл транзакцию, мы не должны ее откатывать… Нет, мы должны, и если вы хотите знать почему, вам нужно прочитать вторую и третью части. Откат транзакции в обработчике CATCH – это категорический императив, у которого нет исключений.

Код повторной генерации ошибки включает такую строку:

DECLARE @msg nvarchar(2048) = error_message()

Встроенная функция error_message() возвращает текст возникшей ошибки. В следующей строке ошибка повторно вызывается с помощью выражения RAISERROR. Это не самый простой способ вызова ошибки, но он работает. Другие способы мы рассмотрим в следующей главе.

Замечание: синтаксис для присвоения начального значения переменной в DECLARE был внедрен в SQL Server 2008. Если у вас SQL Server 2005, вам нужно разбить строку на DECLARE и выражение SELECT.

Финальное выражение RETURN – это страховка. RAISERROR никогда не прерывает выполнение, поэтому выполнение следующего выражения будет продолжено. Пока все процедуры используют TRY-CATCH, а также весь клиентский код обрабатывает исключения, нет повода для беспокойства. Но ваша процедура может быть вызвана из старого кода, написанного до SQL Server 2005 и до внедрения TRY-CATCH. В те времена лучшее, что мы могли делать, это смотреть на возвращаемые значения. То, что вы возвращаете с помощью RETURN, не имеет особого значения, если это не нулевое значение (ноль обычно обозначает успешное завершение работы).

Последнее выражение в процедуре – это END CATCH. Никогда не следует помещать какой-либо код после END CATCH. Кто-нибудь, читающий процедуру, может не увидеть этот кусок кода.

После прочтения теории давайте попробуем тестовый пример:

EXEC insert_data 9, NULL

Результат выполнения:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.

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

CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
go
EXEC outer_sp 8, 8

Результат работы:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Мы получили корректное сообщение об ошибке, но если вы посмотрите на заголовки этого сообщения и на предыдущее поближе, то можете заметить проблему:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

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

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

Мы рассмотрели функцию error_message(), которая возвращает текст сообщения об ошибке. Сообщение об ошибке состоит из нескольких компонентов, и существует своя функция error_xxx() для каждого из них. Мы можем использовать их для повторной генерации полного сообщения, которое содержит оригинальную информацию, хотя и в другом формате. Если делать это в каждом обработчике CATCH, это будет большой недостаток — дублирование кода. Вам не обязательно находиться в блоке CATCH для вызова error_message() и других подобных функций, и они вернут ту же самую информацию, если будут вызваны из хранимой процедуры, которую выполнит блок CATCH.

Позвольте представить вам error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

Первое из того, что делает error_handler_sp – это сохраняет значение всех error_xxx() функций в локальные переменные. Я вернусь к выражению IF через секунду. Вместо него давайте посмотрим на выражение SELECT внутри IF:

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

Цель этого SELECT заключается в форматировании сообщения об ошибке, которое передается в RAISERROR. Оно включает в себя всю информацию из оригинального сообщения об ошибке, которое мы не можем вставить напрямую в RAISERROR. Мы должны обработать имя процедуры, которое может быть NULL для ошибок в обычных скриптах или в динамическом SQL. Поэтому используется функция COALESCE. (Если вы не понимаете форму выражения RAISERROR, я рассказываю о нем более детально во второй части.)

Отформатированное сообщение об ошибке начинается с трех звездочек. Этим достигаются две цели: 1) Мы можем сразу видеть, что это сообщение вызвано из обработчика CATCH. 2) Это дает возможность для error_handler_sp отфильтровать ошибки, которые уже были сгенерированы один или более раз, с помощью условия NOT LIKE ‘***%’ для того, чтобы избежать изменения сообщения во второй раз.

Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

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

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Результат выполнения:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

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

Я покажу еще два метода вызова ошибок. Однако error_handler_sp является моей главной рекомендацией для читателей, которые читают эту часть. Это — простой вариант, который работает на всех версиях SQL Server начиная с 2005. Существует только один недостаток: в некоторых случаях SQL Server генерирует два сообщения об ошибках, но функции error_xxx() возвращают только одну из них, и поэтому одно из сообщений теряется. Это может быть неудобно при работе с административными командами наподобие BACKUPRESTORE, но проблема редко возникает в коде, предназначенном чисто для приложений.

4.2. Использование ;THROW

В SQL Server 2012 Microsoft представил выражение ;THROW для более легкой обработки ошибок. К сожалению, Microsoft сделал серьезную ошибку при проектировании этой команды и создал опасную ловушку.

С выражением ;THROW вам не нужно никаких хранимых процедур. Ваш обработчик CATCH становится таким же простым, как этот:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

Достоинство ;THROW в том, что сообщение об ошибке генерируется точно таким же, как и оригинальное сообщение. Если изначально было два сообщения об ошибках, оба сообщения воспроизводятся, что делает это выражение еще привлекательнее. Как и со всеми другими сообщениями об ошибках, ошибки, сгенерированные ;THROW, могут быть перехвачены внешним обработчиком CATCH и воспроизведены. Если обработчика CATCH нет, выполнение прерывается, поэтому оператор RETURN в данном случае оказывается не нужным. (Я все еще рекомендую оставлять его, на случай, если вы измените свое отношение к ;THROW позже).

Если у вас SQL Server 2012 или более поздняя версия, измените определение insert_data и outer_sp и попробуйте выполнить тесты еще раз. Результат в этот раз будет такой:

Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Имя процедуры и номер строки верны и нет никакого другого имени процедуры, которое может нас запутать. Также сохранены оригинальные номера ошибок.

В этом месте вы можете сказать себе: действительно ли Microsoft назвал команду ;THROW? Разве это не просто THROW? На самом деле, если вы посмотрите в Books Online, там не будет точки с запятой. Но точка с запятой должны быть. Официально они отделяют предыдущее выражение, но это опционально, и далеко не все используют точку с запятой в выражениях T-SQL. Более важно, что если вы пропустите точку с запятой перед THROW, то не будет никакой синтаксической ошибки. Но это повлияет на поведение при выполнении выражения, и это поведение будет непостижимым для непосвященных. При наличии активной транзакции вы получите сообщение об ошибке, которое будет полностью отличаться от оригинального. И еще хуже, что при отсутствии активной транзакции ошибка будет тихо выведена без обработки. Такая вещь, как пропуск точки с запятой, не должно иметь таких абсурдных последствий. Для уменьшения риска такого поведения, всегда думайте о команде как о ;THROW (с точкой с запятой).

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

4.3. Использование SqlEventLog

Третий способ обработки ошибок – это использование SqlEventLog, который я описываю очень детально в третьей части. Здесь я лишь сделаю короткий обзор.

SqlEventLog предоставляет хранимую процедуру slog.catchhandler_sp, которая работает так же, как и error_handler_sp: она использует функции error_xxx() для сбора информации и выводит сообщение об ошибке, сохраняя всю информацию о ней. Вдобавок к этому, она логирует ошибку в таблицу splog.sqleventlog. В зависимости от типа приложения, которое у вас есть, эта таблица может быть очень ценным объектом.

Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

@@procid возвращает идентификатор объекта текущей хранимой процедуры. Это то, что SqlEventLog использует для логирования информации в таблицу. Используя те же тестовые сценарии, получим результат их работы с использованием catchhandler_sp:

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Как вы видите, сообщение об ошибке отформатировано немного не так, как это делает error_handler_sp, но основная идея такая же. Вот образец того, что было записано в таблицу slog.sqleventlog:

logid	logdate	errno	severity	logproc	linenum	msgtext
1	2015-01-25 22:40:24.393	515	16	insert_data	5	Cannot insert …
2	2015-01-25 22:40:24.395	2627	14	insert_data	6	Violation of …

Если вы хотите попробовать SqlEventLog, вы можете загрузить файл sqleventlog.zip. Инструкция по установке находится в третьей части, раздел Установка SqlEventLog.

5. Финальные замечания

Вы изучили основной образец для обработки ошибок и транзакций в хранимых процедурах. Он не идеален, но он должен работать в 90-95% вашего кода. Есть несколько ограничений, на которые стоит обратить внимание:

Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.

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

Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.

Триггеры

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

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

Клиентский код

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

Здесь я только обращу внимание на важную вещь: реакцией на ошибку, возвращенную SQL Server, должно быть завершение запроса во избежание открытых бесхозных транзакций:

IF @@trancount > 0 ROLLBACK TRANSACTION

Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).

6. Конец первой части

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

… и не забывайте добавлять эту строку в начало ваших хранимых процедур:

SET XACT_ABORT, NOCOUNT ON

Источник

Содержание

DECLARE @local_variable (Transact-SQL)
Syntax
Arguments
@local_variable
@cursor_variable_name
column_name
[ COLLATE collation_name ]
DEFAULT
IDENTITY
ROWGUIDCOL
NULL | NOT NULL
PRIMARY KEY
UNIQUE
CLUSTERED | NONCLUSTERED
CHECK
Table variables and row estimates
Remarks
Examples
A. Using DECLARE
B. Using DECLARE with two variables
C. Declaring a variable of type table
D. Declaring a variable of type table, with inline indexes
E. Declaring a variable of user-defined table type
Examples: Azure Synapse Analytics and Analytics Platform System (PDW)
F. Using DECLARE
G. Using DECLARE with two variables
Syntax error in SQL Server while making Dynamic SQL
Scenario:
2 Answers 2
Incorrect syntax near ‘)’ [closed]
2 Answers 2

DECLARE @local_variable (Transact-SQL)

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement. Cursor variables can be declared with this statement and used with other cursor-related statements. After declaration, all variables are initialized as NULL, unless a value is provided as part of the declaration.

Transact-SQL syntax conventions

Syntax

The following syntax is for SQL Server and Azure SQL Database:

The following syntax is for Azure Synapse Analytics and Parallel Data Warehouse:

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Arguments

@local_variable

The name of a variable. Variable names must begin with an at (@) sign. Local variable names must comply with the rules for identifiers.

data_type
Any system-supplied, common language runtime (CLR) user-defined table type, or alias data type. A variable can’t be of text, ntext, or image data type.

For more information about system data types, see Data Types (Transact-SQL). For more information about CLR user-defined types or alias data types, see CREATE TYPE (Transact-SQL).

= value
Assigns a value to the variable in-line. The value can be a constant or an expression, but it must either match the variable declaration type or be implicitly convertible to that type. For more information, see Expressions (Transact-SQL).

@cursor_variable_name

The name of a cursor variable. Cursor variable names must begin with an at (@) sign and conform to the rules for identifiers.

CURSOR
Specifies that the variable is a local cursor variable.

@table_variable_name
The name of a variable of type table. Variable names must begin with an at (@) sign and conform to the rules for identifiers.

Defines the table data type. The table declaration includes column definitions, names, data types, and constraints. The only constraint types allowed are PRIMARY KEY, UNIQUE, NULL, and CHECK. An alias data type can’t be used as a column scalar data type if a rule or default definition is bound to the type.

A subset of information used to define a table in CREATE TABLE. Elements and essential definitions are included here. For more information, see CREATE TABLE (Transact-SQL).

n
A placeholder indicating that multiple variables can be specified and assigned values. When declaring table variables, the table variable must be the only variable being declared in the DECLARE statement.

column_name

The name of the column in the table.

scalar_data_type
Specifies that the column is a scalar data type.

computed_column_expression
An expression defining the value of a computed column. It is computed from an expression using other columns in the same table. For example, a computed column can have the definition cost AS price * qty. The expression can be a noncomputed column name, constant, built-in function, variable, or any combination of these connected by one or more operators. The expression can’t be a subquery or a user-defined function. The expression can’t reference a CLR user-defined type.

[ COLLATE collation_name ]

Specifies the collation for the column. collation_name can be either a Windows collation name or an SQL collation name, and is applicable only for columns of the char, varchar, text, nchar, nvarchar, and ntext data types. If not specified, the column is assigned either the collation of the user-defined data type (if the column is of a user-defined data type) or the collation of the current database.

For more information about the Windows and SQL collation names, see COLLATE (Transact-SQL).

DEFAULT

Specifies the value provided for the column when a value isn’t explicitly supplied during an insert. DEFAULT definitions can be applied to any columns except those defined as timestamp or those with the IDENTITY property. DEFAULT definitions are removed when the table is dropped. Only a constant value, such as a character string; a system function, such as a SYSTEM_USER(); or NULL can be used as a default. To maintain compatibility with earlier versions of SQL Server, a constraint name can be assigned to a DEFAULT.

constant_expression
A constant, NULL, or a system function used as the default value for the column.

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, SQL Server provides a unique incremental value for the column. Identity columns are commonly used with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints can’t be used with an identity column. You must specify both the seed and increment, or neither. If neither is specified, the default is (1,1).

seed
The value used for the first row loaded into the table.

increment
The incremental value added to the identity value of the previous row that was loaded.

ROWGUIDCOL

Indicates that the new column is a row global unique identifier column. Only one uniqueidentifier column per table can be designated as the ROWGUIDCOL column. The ROWGUIDCOL property can be assigned only to a uniqueidentifier column.

NULL | NOT NULL

Indicates if null is allowed in the variable. The default is NULL.

PRIMARY KEY

A constraint that enforces entity integrity for a given column or columns through a unique index. Only one PRIMARY KEY constraint can be created per table.

UNIQUE

A constraint that provides entity integrity for a given column or columns through a unique index. A table can have multiple UNIQUE constraints.

CLUSTERED | NONCLUSTERED

Indicate that a clustered or a nonclustered index is created for the PRIMARY KEY or UNIQUE constraint. PRIMARY KEY constraints use CLUSTERED, and UNIQUE constraints use NONCLUSTERED.

CLUSTERED can be specified for only one constraint. If CLUSTERED is specified for a UNIQUE constraint and a PRIMARY KEY constraint is also specified, the PRIMARY KEY uses NONCLUSTERED.

CHECK

A constraint that enforces domain integrity by limiting the possible values that can be entered into a column or columns.

logical_expression
A logical expression that returns TRUE or FALSE.

Specifies one or more index options. Indexes can’t be created explicitly on table variables, and no statistics are kept on table variables. Starting with SQL SQL Server 2014 (12.x), new syntax was introduced which allows you to create certain index types inline with the table definition. Using this new syntax, you can create indexes on table variables as part of the table definition. In some cases, performance may improve by using temporary tables instead, which provide full index support and statistics.

For a complete description of these options, see CREATE TABLE.

Table variables and row estimates

Table variables don’t have distribution statistics. In many cases, the optimizer will build a query plan on the assumption that the table variable has zero rows or one row. For more information, review table data type — Limitations and restrictions.

For this reason, you should be cautious about using a table variable if you expect a larger number of rows (greater than 100). Consider the following alternatives:

Temp tables may be a better solution than table variables when it is possible for the rowcount to be larger (greater than 100).
For queries that join the table variable with other tables, use the RECOMPILE hint, which will cause the optimizer to use the correct cardinality for the table variable.
In Azure SQL Database and starting with SQL Server 2019 (15.x), the table variable deferred compilation feature will propagate cardinality estimates that are based on actual table variable row counts, providing a more accurate row count for optimizing the execution plan. For more information, see Intelligent query processing in SQL databases.

Variables are often used in a batch or procedure as counters for WHILE, LOOP, or for an IF. ELSE block.

Variables can be used only in expressions, not in place of object names or keywords. To construct dynamic SQL statements, use EXECUTE.

The scope of a local variable is the batch in which it’s declared.

A table variable isn’t necessarily memory resident. Under memory pressure, the pages belonging to a table variable can be pushed out to tempdb .

You can define an inline index in a table variable.

A cursor variable that currently has a cursor assigned to it can be referenced as a source in a:

CLOSE statement
DEALLOCATE statement
FETCH statement
OPEN statement
Positioned DELETE or UPDATE statement
SET CURSOR variable statement (on the right side)

In all of these statements, SQL Server raises an error if a referenced cursor variable exists but doesn’t have a cursor currently allocated to it. If a referenced cursor variable doesn’t exist, SQL Server raises the same error raised for an undeclared variable of another type.

A cursor variable:

Can be the target of either a cursor type or another cursor variable. For more information, see SET @local_variable (Transact-SQL).

Can be referenced as the target of an output cursor parameter in an EXECUTE statement if the cursor variable doesn’t have a cursor currently assigned to it.

Should be regarded as a pointer to the cursor.

Examples

A. Using DECLARE

The following example uses a local variable named @find to retrieve contact information for all last names beginning with Man .

Here is the result set.

B. Using DECLARE with two variables

The following example retrieves the names of Adventure Works Cycles sales representatives who are located in the North American sales territory and have at least $2,000,000 in sales for the year.

C. Declaring a variable of type table

The following example creates a table variable that stores the values specified in the OUTPUT clause of the UPDATE statement. Two SELECT statements follow that return the values in @MyTableVar and the results of the update operation in the Employee table. The results in the INSERTED.ModifiedDate column differ from the values in the ModifiedDate column in the Employee table. This is because the AFTER UPDATE trigger, which updates the value of ModifiedDate to the current date, is defined on the Employee table. However, the columns returned from OUTPUT reflect the data before triggers are fired. For more information, see OUTPUT Clause (Transact-SQL).

D. Declaring a variable of type table, with inline indexes

The following example creates a table variable with a clustered inline index and two nonclustered inline indexes.

The following query returns information about the indexes created in the previous query.

E. Declaring a variable of user-defined table type

The following example creates a table-valued parameter or table variable called @LocationTVP . This requires a corresponding user-defined table type called LocationTableType . For more information about how to create a user-defined table type, see CREATE TYPE (Transact-SQL). For more information about table-valued parameters, see Use Table-Valued Parameters (Database Engine).

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

F. Using DECLARE

The following example uses a local variable named @find to retrieve contact information for all last names beginning with Walt .

G. Using DECLARE with two variables

The following example retrieves uses variables to specify the first and last names of employees in the DimEmployee table.

Источник

Syntax error in SQL Server while making Dynamic SQL

Scenario:

I have a table with 100M+ rows of data so I have created an index with something as:

Now when I execute the following code, it executed in less time:

Now, I want to create this query as dynamic SQL as these parts name_voter asc, 0 , 50 need to be dynamic and will be sent through the backend.

But, when I try to execute

I receive error:

I am not sure but is it because I am passing column name as a parameter? Or is there some other thing. I just want that query to be executed while preventing from SQL injection and with minimal execution time.

I will be here if you need any further information.

2 Answers 2

@marcello has the syntax right, but another thing, to prevent SQL injection you can check the values of @sort_col and @sort_dir (since I assume those are coming from the user). The direction is easy:

Column names are a little more involved. You can just use constants to limit to the output columns from the query:

Or you can make sure the column at least exists in the table, which would allow you to change the columns in the query without having to change the NOT IN list, and also to allow sorting by a column not present in the output of the query (even if that might not always make sense):

I wrote about this and other techniques in a two-part series:

Источник

Incorrect syntax near ‘)’ [closed]

Too localized — this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background).

Closed 5 years ago .

I’m trying to execute the following stored procedure:

But I when I execute this code:

I get this error message:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’.

This code was working for one year and now it doesn’t. Our version control does not seem to help either, and, unfortunately, the logic does not seem straightforward to me.

One thought was about the version of SQL Server causing breaking changes, but I am not convinced.

How would I go about troubleshooting this issue? Are there any good industry practices for tracking down script issues when dynamic sql is involved?

I need to verify where the breaking code starts, not necessarily where the syntax error occurs.

2 Answers 2

While it’s certainly possible that someone changed your stored procedure and broke it in the process, the simplest explanation for something like this breaking is a change in the input data that causes it to break.

In the comments, a number of people tried your stored procedure in various versions of SQL Server, and it ran successfully. This makes it even more likely that the problem is a change in the data being provided to the stored procedure.

If you can think of a table that’s using a data type that none of your tables has used before, that could cause your procedure to start running a branch that it had never needed to run before, causing an error.

You can troubleshoot this using the following steps:

You’ve already got a statement that prints out the dynamic SQL you execute to your «Messages» window in SSMS, before it executes it.

Look at the output in that window carefully. Are any tables processed, or are you failing on the very first table?

If you are failing on the very first table, before @script_sql is even printed once, then try running the stored procedure manually. Using SSMS, in the Object Explorer window:

open a Database Engine connection to your server;
expand your server; then Databases; then your database; then Programmability; then Stored Procedures.
Right-click on your stored procedure, and select «Script Stored Procedure As», «CREATE To», «New Query Editor Window».
In the query editor window, modify the code that creates your stored procedure so you can run the code directly. remove or comment out the CREATE PROCEDURE line; change the parameter list to DECLARE statements; and comment out or remove the AS .

Figure out the first table name your script is trying to process. Manually set @nom_table to this table name in your DECLARE statement (or in a SET statement right afterwards). Now, run the modified code from your stored procedure. See exactly where it stops (add additional PRINT statements if necessary to determine exactly where it stops).

If you get to the point where @script_sql is printed out, try copying the statement and running that manually, in a new query window. If that fails, look at the SQL statement carefully.

Get the length of @script_sql , and compare that with what was output; beyond a certain length, PRINT will truncate your value (so your wouldn’t be getting all of @script_sql in the «Messages» window). You can overcome this by printing substrings of @script_sql
If you don’t see any obvious errors in the statement, try running parts of it only. Can you create the table with just the first column? just the first two? etc. As you include additional columns, you will eventually find one or more that are causing issues. For each test, start from the full code in @script_sql , to minimize the likelihood of introducing errors by repeated cutting and pasting.

One of the current answers points out a couple of potential issues in your code; I’ll point out one more. I don’t think the line:

is working as you’d expect it to. I believe it’s intended to strip a trailing comma from the SQL statement before adding the closing parenthesis. However, it’s actually checking if the first character in @script_sql is a comma, not the last. Try this instead:

Finally — you can get an idea of whether anyone has changed your procedure from SSMS. If you don’t still have everything expanded, follow the directions from step 2 above to expand your server and database to the point where you see the «Stored Procedures» folder. Left-click on the folder, then go to the «Windows» menu and choose «Object Explorer Details». You’ll get a view of your stored procedure that includes «Create Date» and «Date Last Modified» (if you don’t see those, right-click on the headers for the columns you do see, and select those columns so you can see them.) Check the create date and last modified date for the procedure; has it been changed recently?

If so, then hopefully you have an old version of the stored procedure in a text or .sql file somewhere; or, you have an old backup of your database (you can restore this under a different name, and script out the stored procedure from the backup. You can then remove the old restored DB, and try the older version of the stored procedure, to see if it works).

You should also be using NVARCHAR for all of this instead of VARCHAR . And change the declaration of @code_colonne VARCHAR(1000) to be NVARCHAR(MAX) . Something might be being silently truncated, or how code_colonne is being built, such as a blank or still NA (instead of a NULL ) for Longest causing the resulting type to be either CHAR() or CHAR(NA) .

You could also run a Profiler trace, to see what’s going on during the procedure execution.

Источник

Привет! Представляю вашему вниманию перевод статьи «Error and Transaction Handling in SQL Server. Part One – Jumpstart Error Handling» автора Erland Sommarskog.

1. Введение

Все статьи описывают обработку ошибок и транзакций в SQL Server для версии 2005 и более поздних версий.

1.1 Зачем нужна обработка ошибок?

2. Основные команды

2.1 TRY-CATCH

BEGIN TRY
   <обычный код>
END TRY
BEGIN CATCH
   <обработка ошибок>
END CATCH

Вот очень быстрый пример:

BEGIN TRY
   DECLARE @x int
   SELECT @x = 1/0
   PRINT 'Not reached'
END TRY
BEGIN CATCH 
   PRINT 'This is the error: ' + error_message()
END CATCH

Результат выполнения: This is the error: Divide by zero error encountered.

CREATE PROCEDURE inner_sp AS
   BEGIN TRY
      PRINT 'This prints'
      SELECT * FROM NoSuchTable
      PRINT 'This does not print'
   END TRY
   BEGIN CATCH
      PRINT 'And nor does this print'
   END CATCH
go
EXEC inner_sp

Выходные данные:

This prints
Msg 208, Level 16, State 1, Procedure inner_sp, Line 4
Invalid object name 'NoSuchTable'

CREATE PROCEDURE outer_sp AS
   BEGIN TRY
      EXEC inner_sp
   END TRY
   BEGIN CATCH
      PRINT 'The error message is: ' + error_message()
   END CATCH
go
EXEC outer_sp

Теперь мы получим на выходе это:

This prints
The error message is: Invalid object name 'NoSuchTable'.

На этот раз ошибка была перехвачена, потому что сработал внешний обработчик CATCH.

2.2 SET XACT_ABORT ON

В начало ваших хранимых процедур следует всегда добавлять это выражение:

SET XACT_ABORT, NOCOUNT ON

SET NOCOUNT ON
SET XACT_ABORT ON

3. Основной пример обработки ошибок

Для примера я буду использовать эту простую таблицу.

CREATE TABLE sometable(a int NOT NULL,
                       b int NOT NULL,
                       CONSTRAINT pk_sometable PRIMARY KEY(a, b))

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

CREATE PROCEDURE insert_data @a int, @b int AS 
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      BEGIN TRANSACTION
      INSERT sometable(a, b) VALUES (@a, @b)
      INSERT sometable(a, b) VALUES (@b, @a)
      COMMIT TRANSACTION
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()  
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH

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

Эти три действия должны всегда быть там. Мы можете возразить, что строка

IF @@trancount > 0 ROLLBACK TRANSACTION

Код повторной генерации ошибки включает такую строку:

DECLARE @msg nvarchar(2048) = error_message()

После прочтения теории давайте попробуем тестовый пример:

EXEC insert_data 9, NULL

Результат выполнения:

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.

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

CREATE PROCEDURE outer_sp @a int, @b int AS
   SET XACT_ABORT, NOCOUNT ON
   BEGIN TRY
      EXEC insert_data @a, @b
   END TRY
   BEGIN CATCH
      IF @@trancount > 0 ROLLBACK TRANSACTION
      DECLARE @msg nvarchar(2048) = error_message()
      RAISERROR (@msg, 16, 1)
      RETURN 55555
   END CATCH
go
EXEC outer_sp 8, 8

Результат работы:

Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

Msg 50000, Level 16, State 1, Procedure insert_data, Line 12
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

Позвольте представить вам error_handler_sp:

CREATE PROCEDURE error_handler_sp AS
 
   DECLARE @errmsg   nvarchar(2048),
           @severity tinyint,
           @state    tinyint,
           @errno    int,
           @proc     sysname,
           @lineno   int
           
   SELECT @errmsg = error_message(), @severity = error_severity(),
          @state  = error_state(), @errno = error_number(),
          @proc   = error_procedure(), @lineno = error_line()
       
   IF @errmsg NOT LIKE '***%'
   BEGIN
      SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                       ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                       ltrim(str(@errno)) + ': ' + @errmsg
   END
   RAISERROR('%s', @severity, @state, @errmsg)

SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + 
                 ', Line ' + ltrim(str(@lineno)) + '. Errno ' + 
                 ltrim(str(@errno)) + ': ' + @errmsg

Вот как обработчик CATCH должен выглядеть, когда вы используете error_handler_sp:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC error_handler_sp
   RETURN 55555
END CATCH

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

EXEC insert_data 8, NULL
EXEC outer_sp 8, 8

Результат выполнения:

Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20
*** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20
*** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

4.2. Использование ;THROW

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   ;THROW
   RETURN 55555
END CATCH

Msg 515, Level 16, State 2, Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 2627, Level 14, State 1, Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

4.3. Использование SqlEventLog

Для использования SqlEventLog, ваш обработчик CATCH должен быть таким:

BEGIN CATCH
   IF @@trancount > 0 ROLLBACK TRANSACTION
   EXEC slog.catchhandler_sp @@procid
   RETURN 55555
END CATCH

Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125
{515} Procedure insert_data, Line 5
Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails.
Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125
{2627} Procedure insert_data, Line 6
Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8).

logid	logdate	errno	severity	logproc	linenum	msgtext
1	2015-01-25 22:40:24.393	515	16	insert_data	5	Cannot insert …
2	2015-01-25 22:40:24.395	2627	14	insert_data	6	Violation of …

5. Финальные замечания

Как мы видели, ошибки компиляции не могут быть перехвачены в той же процедуре, в которой они возникли, а только во внешней процедуре.
Пример не работает с пользовательскими функциями, так как ни TRY-CATCH, ни RAISERROR нельзя в них использовать.
Когда хранимая процедура на Linked Server вызывает ошибку, эта ошибка может миновать обработчик в хранимой процедуре на локальном сервере и отправиться напрямую клиенту.
Когда процедура вызвана как INSERT-EXEC, вы получите неприятную ошибку, потому что ROLLBACK TRANSACTION не допускается в данном случае.
Как упомянуто выше, если вы используете error_handler_sp или SqlEventLog, мы потеряете одно сообщение, когда SQL Server выдаст два сообщения для одной ошибки. При использовании ;THROW такой проблемы нет.

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

Перед тем как закончить, я хочу кратко коснуться триггеров и клиентского кода.

Триггеры

Клиентский код

IF @@trancount > 0 ROLLBACK TRANSACTION

Это также применимо к знаменитому сообщению Timeout expired (которое является не сообщением от SQL Server, а от API).

6. Конец первой части

… и не забывайте добавлять эту строку в начало ваших хранимых процедур:

SET XACT_ABORT, NOCOUNT ON

Автор: Schekotka

Источник

Источник

User-1992748085 posted

I’m currently getting this error when trying to run my SQL query :

[Err] 42000 — [SQL Server] Must declare the scalar variable «@StartDate».
42000 — [SQL Server]Must declare the scalar variable «@StartDate».

My code:

Declare  @StartDate DATETIME 
SET @StartDate = '2018-06-01'

Declare @EndDate DATETIME
set @EndDate = '2018-06-30'

;

with t_Redemption as (

select Customer, isNull(sum([v2_pointredeem]),0) [v2_pointredeem], isNull(sum([v5_Gift]),0) [v5_Gift]

from (

select isNull(c.[member id],'') as Customer,

case

when convert(date,r.[Redemption Date]) < Cast(@StartDate as date) then

[TotalProductPoints]

else 0

end as [v2_pointredeem],

case

when cast( r.[Redemption Date] as date) >= Cast( @StartDate as date) 

and cast( r.[Redemption Date] as date) <= Cast(@EndDate as date) then

[TotalProductPoints]

else 0

end as [v5_Gift]

from QF_Redemption r
left join qf_customer c
on r.Customer = c.[ID]

) t

group by Customer

),

t_Transaction as (

select Customer, isNull(sum([v1]),0) [v1], isNull(sum([v4_SalesTransaction]),0) [v4_SalesTransaction], 

isNull(sum([v3_ExpiredPoint]),0) [v3_ExpiredPoint], isNull(sum([v7_Expired]),0) [v7_Expired]

from (

select Customer,

case

when convert(date,[Transaction Date]) < Cast(@StartDate as date) then

(Cast(IsNull([ExtraPoints],0) as int)+Cast(IsNull([TotalPoints],0) as int)+Cast(IsNull([TotalMultiplier],0) as int))

else 0

end as [v1],

case

when convert(date,[Transaction Date]) >= Cast(@StartDate as date) 

and convert(date,[Transaction Date]) <= Cast(@EndDate as date) then

(Cast(IsNull([ExtraPoints],0) as int)+Cast(IsNull([TotalPoints],0) as int)+Cast(IsNull([TotalMultiplier],0) as int))

else 0

end as [v4_SalesTransaction],

case

when convert(date,[ValidityDate]) < Cast(@StartDate as date) then
-- When [ValidityDate] >= Cast(@StartDate as date) and [ValidityDate] <= Cast(@EndDate as date) then
(	Cast(IsNull([ExtraPoints],0) as int)+

Cast(IsNull([TotalPoints],0) as int)+

Cast(IsNull([TotalMultiplier],0) as int)-

Cast(IsNull([TotalPointsRedeemed], 0) as int))

else 0

end as [v3_ExpiredPoint],

case

when convert(date,[ValidityDate]) >= Cast(@StartDate as date) 

and convert(date,[ValidityDate]) <= Cast(@EndDate as date) then 

(	Cast(IsNull([ExtraPoints],0) as int)+

Cast(IsNull([TotalPoints],0) as int)+

Cast(IsNull([TotalMultiplier],0) as int)-

Cast(IsNull([TotalPointsRedeemed], 0) as int))

else 0

end as [v7_Expired]

from QF_Transaction

--where outlet = @Outlet

) t

group by Customer

)
 
 
Select  'c' + customer , sum(v1)-sum(v2_pointredeem) - sum(v3_ExpiredPoint) as OB, sum(v4_SalesTransaction) SalesTransaction,sum(v5_Gift) Gift_Product,sum(v7_Expired) Expired from (
select customer,v1, 0 v2_pointredeem,   v3_ExpiredPoint,   v4_SalesTransaction, 
	0 v5_Gift,   v7_Expired
from t_Transaction
union all
select customer,0 v1, v2_pointredeem, 0 v3_ExpiredPoint, 0 v4_SalesTransaction,  v5_Gift, 0 v7_Expired
from t_Redemption
) ttt
 --Where v4_SalesTransaction> 0 or v5_Gift> 0 or v7_Expired > 0
  group by customer

I would greatly appreciate any help you can give me in working this problem.

Cheers,

Lihardo

</div> </div>

Источник

title

description

author

ms.author

ms.date

ms.service

ms.subservice

ms.topic

f1_keywords

helpviewer_keywords

dev_langs

monikerRange

RAISERROR (Transact-SQL)

rwestMSFT

randolphwest

08/09/2022

sql

t-sql

reference

RAISERROR

RAISERROR_TSQL

RAISEERROR_TSQL

sysmessages system table

errors [SQL Server], RAISERROR statement

user-defined error messages [SQL Server]

system flags

generating errors [SQL Server]

TRY block [SQL Server]

recording errors

ad hoc messages

RAISERROR statement

CATCH block

messages [SQL Server], RAISERROR statement

TSQL

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

[!INCLUDE sql-asdb-asdbmi-asa-pdw]

[!NOTE]
The RAISERROR statement does not honor SET XACT_ABORT. New applications should use THROW instead of RAISERROR.

Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view, or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY...CATCH construct. New applications should use THROW instead.

:::image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false»::: Transact-SQL syntax conventions

Syntax

Syntax for SQL Server and Azure SQL Database:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { , severity, state }
    [ , argument [ , ...n ] ] )
    [ WITH option [ , ...n ] ]

Syntax for Azure Synapse Analytics and Parallel Data Warehouse:

RAISERROR ( { msg_str | @local_variable }
    { , severity, state }
    [ , argument [ , ...n ] ] )
    [ WITH option [ , ...n ] ]

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

msg_id

A user-defined error message number stored in the sys.messages catalog view using sp_addmessage. Error numbers for user-defined error messages should be greater than 50000. When msg_id is not specified, RAISERROR raises an error message with an error number of 50000.

msg_str

A user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated. Note that substitution parameters consume more characters than the output shows because of internal storage behavior. For example, the substitution parameter of %d with an assigned value of 2 actually produces one character in the message string but also internally takes up three additional characters of storage. This storage requirement decreases the number of available characters for message output.

When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. Conversion specifications have this format:

% [[flag] [width] [. precision] [{h | l}]] type

The parameters that can be used in msg_str are:

flag

A code that determines the spacing and justification of the substituted value.

Code	Prefix or justification	Description
— (minus)	Left-justified	Left-justify the argument value within the given field width.
+ (plus)	Sign prefix	Preface the argument value with a plus (+) or minus (-) if the value is of a signed type.
0 (zero)	Zero padding	Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored.
# (number)	0x prefix for hexadecimal type of x or X	When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored.
‘ ‘ (blank)	Space padding	Preface the output value with blank spaces if the value is signed and positive. This is ignored when included with the plus sign (+) flag.

width

An integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.

An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

precision

The maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

For integer values, precision is the minimum number of digits printed.

An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h | l} type

Used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

Type specification	Represents
d or i	Signed integer
o	Unsigned octal
s	String
u	Unsigned integer
x or X	Unsigned hexadecimal

These type specifications are based on the ones originally defined for the printf function in the C standard library. The type specifications used in RAISERROR message strings map to [!INCLUDEtsql] data types, while the specifications used in printf map to C language data types. Type specifications used in printf are not supported by RAISERROR when [!INCLUDEtsql] does not have a data type similar to the associated C data type. For example, the %p specification for pointers is not supported in RAISERROR because [!INCLUDEtsql] does not have a pointer data type.

To convert a value to the [!INCLUDEtsql] bigint data type, specify %I64d.

@local_variable

Is a variable of any valid character data type that contains a string formatted in the same manner as msg_str. @local_variable must be char or varchar, or be able to be implicitly converted to these data types.

severity

The user-defined severity level associated with this message. When using msg_id to raise a user-defined message created using sp_addmessage, the severity specified on RAISERROR overrides the severity specified in sp_addmessage.

For severity levels from 19 through 25, the WITH LOG option is required. Severity levels less than 0 are interpreted as 0. Severity levels greater than 25 are interpreted as 25.

[!CAUTION]
Severity levels from 20 through 25 are considered fatal. If a fatal severity level is encountered, the client connection is terminated after receiving the message, and the error is logged in the error and application logs.

You can specify -1 to return the severity value associated with the error as shown in the following example.

RAISERROR (15600, -1, -1, 'mysp_CreateCustomer');

[!INCLUDEssResult]

Msg 15600, Level 15, State 1, Line 1
An invalid parameter or option was specified for procedure 'mysp_CreateCustomer'.

state

An integer from 0 through 255. Negative values default to 1. Values larger than 255 should not be used.

If the same user-defined error is raised at multiple locations, using a unique state number for each location can help find which section of code is raising the errors.

argument

The parameters used in the substitution for variables defined in msg_str or the message corresponding to msg_id. There can be 0 or more substitution parameters, but the total number of substitution parameters cannot exceed 20. Each substitution parameter can be a local variable or any of these data types: tinyint, smallint, int, char, varchar, nchar, nvarchar, binary, or varbinary. No other data types are supported.

option

A custom option for the error and can be one of the values in the following table.

Value Description

Value	Description
`LOG`	Logs the error in the error log and the application log for the instance of the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] [!INCLUDEssDE]. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG. [!INCLUDEapplies] [!INCLUDEssNoVersion]
`NOWAIT`	Sends messages immediately to the client. [!INCLUDEapplies] [!INCLUDEssNoVersion], [!INCLUDEssSDS]
`SETERROR`	Sets the `@@ERROR` and `ERROR_NUMBER` values to msg_id or 50000, regardless of the severity level. [!INCLUDEapplies] [!INCLUDEssNoVersion], [!INCLUDEssSDS]

LOG

Logs the error in the error log and the application log for the instance of the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] [!INCLUDEssDE]. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.

[!INCLUDEapplies] [!INCLUDEssNoVersion]

NOWAIT

Sends messages immediately to the client.

[!INCLUDEapplies] [!INCLUDEssNoVersion], [!INCLUDEssSDS]

SETERROR

Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.

[!INCLUDEapplies] [!INCLUDEssNoVersion], [!INCLUDEssSDS]

Remarks

The errors generated by RAISERROR operate the same as errors generated by the [!INCLUDEssDE] code. The values specified by RAISERROR are reported by the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, ERROR_STATE, and @@ERROR system functions. When RAISERROR is run with a severity of 11 or higher in a TRY block, it transfers control to the associated CATCH block. The error is returned to the caller if RAISERROR is run:

Outside the scope of any TRY block.
With a severity of 10 or lower in a TRY block.
With a severity of 20 or higher that terminates the database connection.

CATCH blocks can use RAISERROR to rethrow the error that invoked the CATCH block by using system functions such as ERROR_NUMBER and ERROR_MESSAGE to retrieve the original error information. @@ERROR is set to 0 by default for messages with a severity from 1 through 10.

When msg_id specifies a user-defined message available from the sys.messages catalog view, RAISERROR processes the message from the text column using the same rules as are applied to the text of a user-defined message specified using msg_str. The user-defined message text can contain conversion specifications, and RAISERROR will map argument values into the conversion specifications. Use sp_addmessage to add user-defined error messages and sp_dropmessage to delete user-defined error messages.

RAISERROR can be used as an alternative to PRINT to return messages to calling applications. RAISERROR supports character substitution similar to the functionality of the printf function in the C standard library, while the [!INCLUDEtsql] PRINT statement does not. The PRINT statement is not affected by TRY blocks, while a RAISERROR run with a severity of 11 to 19 in a TRY block transfers control to the associated CATCH block. Specify a severity of 10 or lower to use RAISERROR to return a message from a TRY block without invoking the CATCH block.

Typically, successive arguments replace successive conversion specifications; the first argument replaces the first conversion specification, the second argument replaces the second conversion specification, and so on. For example, in the following RAISERROR statement, the first argument of N'number' replaces the first conversion specification of %s; and the second argument of 5 replaces the second conversion specification of %d.

RAISERROR (N'This is message %s %d.', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.
-- The message text returned is: This is message number 5.
GO

If an asterisk (*) is specified for either the width or precision of a conversion specification, the value to be used for the width or precision is specified as an integer argument value. In this case, one conversion specification can use up to three arguments, one each for the width, precision, and substitution value.

For example, both of the following RAISERROR statements return the same string. One specifies the width and precision values in the argument list; the other specifies them in the conversion specification.

RAISERROR (N'<<%*.*s>>', -- Message text.
           10, -- Severity,
           1, -- State,
           7, -- First argument used for width.
           3, -- Second argument used for precision.
           N'abcde'); -- Third argument supplies the string.
-- The message text returned is: <<    abc>>.
GO
RAISERROR (N'<<%7.3s>>', -- Message text.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO

Permissions

Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25 can only be specified by members of the sysadmin fixed server role or users with ALTER TRACE permissions.

Examples

A. Returning error information from a CATCH block

The following code example shows how to use RAISERROR inside a TRY block to cause execution to jump to the associated CATCH block. It also shows how to use RAISERROR to return information about the error that invoked the CATCH block.

[!NOTE]
RAISERROR only generates errors with state from 1 through 127. Because the [!INCLUDEssDE] may raise errors with state 0, we recommend that you check the error state returned by ERROR_STATE before passing it as a value to the state parameter of RAISERROR.

BEGIN TRY
    -- RAISERROR with severity 11-19 will cause execution to
    -- jump to the CATCH block.
    RAISERROR ('Error raised in TRY block.', -- Message text.
               16, -- Severity.
               1 -- State.
               );
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    SELECT
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
               @ErrorSeverity, -- Severity.
               @ErrorState -- State.
               );
END CATCH;

B. Creating an ad hoc message in sys.messages

The following example shows how to raise a message stored in the sys.messages catalog view. The message was added to the sys.messages catalog view by using the sp_addmessage system stored procedure as message number 50005.

EXEC sp_addmessage @msgnum = 50005,
              @severity = 10,
              @msgtext = N'<<%7.3s>>';
GO
RAISERROR (50005, -- Message id.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO
EXEC sp_dropmessage @msgnum = 50005;
GO

C. Using a local variable to supply the message text

The following code example shows how to use a local variable to supply the message text for a RAISERROR statement.

DECLARE @StringVariable NVARCHAR(50);
SET @StringVariable = N'<<%7.3s>>';

RAISERROR (@StringVariable, -- Message text.
           10, -- Severity,
           1, -- State,
           N'abcde'); -- First argument supplies the string.
-- The message text returned is: <<    abc>>.
GO

What’s the Right Way to Declare a Variable in SQL?

To declare a variable in SQL, use the DECLARE statement. This function specifies some or all of the attributes of a name. In SQL, the Declare function initializes a variable by assigning it a name and a data type. A variable name must start with the @ sign.

The below example declares a variable @modelYear

DECLARE @modelYear SMALLINT;

The data type of the @modelYear variable is SMALLINT. When a variable is declared, its value is set to NULL by default. You can add an optional keyword AS between variable name and data type, as shown in the example below:

DECLARE @modelYear AS SMALLINT;

If you have several variables to declare, use commas to separate them, as shown below:

DECLARE @modelYear SMALLINT,

@productName VARCHAR(MAX);

If you don’t declare a variable, you will receive the error: Must declare the scalar variable name.

– Scalar Variable Error SQL Example

A variable declaration will specify the name and data type of the variable. Still, a variable declaration will be an initial value for most data types.

The variable name should be a valid user-defined identifier. Here is an example on how you can declare it:

DECLARE
partNumber NUMBER(8);
partName VARCHAR2(30);
inStock BOOLEAN;
partPrice NUMBER(8,3);
partDescription VARCHAR2(40);
BEGIN
NULL;
END;
/

Also, you can declare a variable and constant with initial values as shown below:

DECLARE
hoursWorked INTG := 50;
employeeCount INTG := 0;
pi CONSTANT REAL := 3.14159;
radius REAL := 6;
area REAL := (pi * radius**6);
BEGIN
NULL;
END;
/

If you do not define an initial value for a variable, assign a value to it prior to using it in any other context.

You can impose the NOT NULL constraint on a scalar variable. This constraint prevents assigning a null value to the item.

The item can acquire this constraint either implicitly or explicitly.

Scalar Variable Warning Causes and Reasons

This error will occur in most cases if you try to use an undeclared variable. Let’s look at some scenarios that might lead to this error.

– Using an Undeclared Variable

If you do not declare a variable, you will receive this error message. The following example attempts to execute a statement without declaring it.

The error must declare the scalar variable @AuthorName pops out when executing the statement. The primary reason for the error is that the variable @AuthorName is used in the PRINT statement without declaring it. The SQL server does not allow this approach.

– Local Declared Variable After Go Statement

This scenario will also throw the error. Here is an example to illustrate every step that leads to the issue:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
PRINT @AuthorName
GO
PRINT @AuthorName

When you execute the statement, the output will be:

Must declare the scalar variable “@AuthorName.”

So, what’s wrong? In this example, the variable @AuthorName is used after the batch separator GO Statement in the PRINT statement—the scope of the local variables in the batch in which it is declared.

– Execute a Local Declared Variable in Dynamic SQL Statement

Dynamic SQL lets you create SQL statements dynamically at runtime. You can create more general-purpose and flexible statements, as the full text of the SQL statements may be unknown at compilation. However, errors can occur if not well executed. Check the example below:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
EXECUTE (‘PRINT @AuthorName’)

You will get the must declare scalar variable “@AuthorName” error when you run the code.

Typically, the variable @AuthorName is used in the statement executed by EXECUTE statement. EXECUTE statement does not have the visibility of the variables declared outside it. This results in the error.

The Best Fixes for Scalar Variable Failure

There are severe fixes available to solve the issue and get your code running smoothly. Check the solutions below to choose the one that is best for you.

– Solving Undeclared Variable

This is easy: declare the @AuthorName variable before using it in the PRINT statement as below:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
PRINT @AuthorName

Output:

In the example above, we use the DECLARE statement (a requirement in SQL server) to declare the variable. The second step specifies the name of the variable. Remember that all local names start with @ as it is a syntax necessity. Lastly, we have defined the data type of the variable.

Note that if we do not make any initial value assigned to a variable, it is initialized as a NULL.

– Go Statement or Batch Separator Fix

You will need to redeclare the @AuthorName variable before using it in the PRINT statement after the GO statement, as shown below.

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
PRINT @AuthorName
GO
DECLARE @AuthorName VARCHAR(100) = ‘Buscar’
PRINT @AuthorName

The output after executing the statement will be:

Vishal Balasubramanian
Buscar

Using the Go statement creates a new branch where the variables declared are not visible past the statement. So, all variables declared at the top of the file will not be accessible if the go statement is executed.

Although the variable @AuthorName is declared twice, the code still works. Here is why there is a batch separator, the Go statement, between the two variable declarations. The variable declared before the go statement is not accessed after the Go statement.

Typically, the SQL Server Management Studio ( SSMS) sends the first batch of statements to the SQL engine first for executions. It will send the second batch of statements after the Go statement to the SQL engine for execution when execution is over.

– Solving Local Declared Variable in Dynamic SQL Statement

Here is how to resolve the issue in the above statement:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
EXECUTE (‘PRINT ”’ + @AuthorName + ”” )

Output:

Alternatively, you can solve the above issue using the SP_EXECUTESQL statement, which allows parameterized statement. Check out this code for insights:

DECLARE @AuthorName VARCHAR(100) = ‘Vishal Balasubramanian’
EXECUTE SP_EXECUTESQL N’PRINT @AuthorName’,
N’@AuthorName VARCHAR(100)’,@AuthorName

The output after executing the program will be:

While executing a dynamic SQL, it is executed in a different scope. Therefore, if you declare your variables in the same scope as the VARCHAR variable with the SQL statement, you definitely will get that error.

But it is pretty impossible to access the variable from outside the dynamic SQL. You can resolve that issue in two ways:

Insert the code that uses the variable in the dynamic SQL
Insert the value in a temporary table (table will be seen outside the scope) and use it outside dynamic – execute SELECT against the temporary table.
Parameterize and do not inject raw string in a dynamically generated statemen. SQL injection is a huge problem, rendering it an incorrect way of doing it.

– Other Causes and Fixes of Undeclared Variable

You might also get an “Undeclared variable” error when declaring a variable but not assigning it. Check out this example:

SELECT idSubscriber
INTO newsletterToSend
FROM subscribers

When you execute the statement, it generates an error: #1327 – Undeclared variable: newsletterToSend.

The problem, in this case, is that SQL does not support SELECT INTO [table] syntax. Instead, it only supports SELECT INTO [variable], and you can declare each variable individually and assign a value for every parameter in case of multiple SQL variables.

To solve this issue, you will need to use the CREATE TABLE syntax with SELECT, as shown below:

CREATE TABLE bar ([column list]) SELECT * FROM foo;

The below query will work fine:

CREATE TABLE New_Table_name
SELECT * FROM Original_Table_name;

SQL allows you to use two methods to assign values to a variable:

SET statement
SELECT statement

To use set, declare a variable and then assign a value using the SET statement.

For example:

DECLARE @AuthorName VARCHAR(100)
SET @TestVariable = ‘Vishal Balasubramanian’
PRINT @AuthorName

The output after executing the statement is:

To use the SELECT statement, insert it in the place of the SET statement.

You can use the SELECT statement to assign a value to a variable from the table, view, or scalar-valued functions. Nevertheless, if not done well, the error: Must declare the scalar variable in select statement can occur.

Must Declare the Scalar Variable Stored Procedure

In SQL, stored procedures let you create and store SQL queries and later execute them on a server. Their primary purpose is to hide direct SQL queries from the code and improve the performance of database operations such as selecting, updating, and deleting data.

While using them, you may encounter errors such as must declare the scalar variable @date. When this error occurs, you must declare and initialize this variable first.

Conclusion

While creating SQL queries, errors are inevitable, even if you are very careful. You need to reread your code and learn a few tricks for solving mistakes. Here is the rundown of our article:

SQL requires that you declare statements using the DECLARE statement; you will get an error if you don’t.
Always start local names with @ because it is a syntax necessity. So, you declare a variable as DECLARE @Name.
A batch separator, the Go statement separates two statements ensuring that a variable declared before the go statement is not accessed after the Go statement.
Dynamic SQL is executed in a different scope. Therefore, if you declare your variables in the same scope as the VARCHAR variable with the SQL statement, you definitely will get that error.

Since you know the possible causes and fixes, you can easily avoid the error or fix it soon as it pops up.

Author
Recent Posts

Position Is Everything: Your Go-To Resource for Learn & Build: CSS,JavaScript,HTML,PHP,C++ and MYSQL.

Источник

1. Введение

1.1 Зачем нужна обработка ошибок?

2. Основные команды

2.1 TRY-CATCH

2.2 SET XACT_ABORT ON

3. Основной пример обработки ошибок

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

4.2. Использование ;THROW

4.3. Использование SqlEventLog

5. Финальные замечания

Триггеры

Клиентский код

6. Конец первой части

DECLARE @local_variable (Transact-SQL)

Syntax

Arguments

@local_variable

@cursor_variable_name

column_name

[ COLLATE collation_name ]

DEFAULT

IDENTITY

ROWGUIDCOL

NULL | NOT NULL

PRIMARY KEY

UNIQUE

CLUSTERED | NONCLUSTERED

CHECK

Table variables and row estimates

Examples

A. Using DECLARE

B. Using DECLARE with two variables

C. Declaring a variable of type table

D. Declaring a variable of type table, with inline indexes

E. Declaring a variable of user-defined table type

Examples: Azure Synapse Analytics and Analytics Platform System (PDW)

F. Using DECLARE

G. Using DECLARE with two variables

Syntax error in SQL Server while making Dynamic SQL

Scenario:

2 Answers 2

Incorrect syntax near ‘)’ [closed]

2 Answers 2

1. Введение

1.1 Зачем нужна обработка ошибок?

2. Основные команды

2.1 TRY-CATCH

2.2 SET XACT_ABORT ON

3. Основной пример обработки ошибок

4. Три способа генерации ошибки

4.1 Использование error_handler_sp

4.2. Использование ;THROW

4.3. Использование SqlEventLog

5. Финальные замечания

Триггеры

Клиентский код

6. Конец первой части

Syntax

Arguments

msg_id

msg_str

@local_variable

severity

state

argument

option

Remarks

Permissions

Examples

A. Returning error information from a CATCH block

B. Creating an ad hoc message in sys.messages

C. Using a local variable to supply the message text

See also

– Scalar Variable Declaration

Syntax:

What’s the Right Way to Declare a Variable in SQL?

– Scalar Variable Error SQL Example

Scalar Variable Warning Causes and Reasons

– Using an Undeclared Variable