Каковы наилучшие методы написания хранимой процедуры sql

Ответ 1

Вот мои рекомендации по обработке ошибок хранимых процедур.

• Для повышения производительности вызывайте каждую хранимую процедуру, используя ее полное имя: имя сервера, имя базы данных, имя схемы (владельца) и имя процедуры.
• В сценарии, который создает каждую хранимую процедуру, явно укажите, каким ролям разрешено выполнять процедуру, например, public или что-то еще.
• Используйте sysmessage, sp_addmessage и заполнители вместо жестко закодированных сообщений об ошибках.
• При использовании sp_addmessage и sysmessages всегда используйте номер сообщения об ошибке 50001 или более.
• В RAISERROR всегда указывайте уровень серьезности <= 10 для предупреждающих сообщений.
• В RAISERROR всегда указывайте уровень серьезности от 11 до 16 для сообщений об ошибках.
• Помните, что использование RAISERROR не всегда прерывает выполнение какого-либо пакета, даже в контексте триггера.
• Сохраните @@error в локальной переменной перед ее использованием или запросом.
• Сохраните @@rowcount в локальной переменной, прежде чем использовать ее или запрашивать.
• Для хранимой процедуры используйте возвращаемое значение, чтобы указать только успех/сбой, а не какую-либо другую/дополнительную информацию.
• Возвращаемое значение для хранимой процедуры должно быть установлено на 0, чтобы указать успешность, ненулевое, чтобы указать на неудачу.
• Установить ANSI_WARNINGS ON - это обнаруживает нулевые значения в любом совокупном назначении и любом назначении, которое превышает максимальную длину символа или двоичного столбца.
• Установите NOCOUNT ON по многим причинам.
• Тщательно продумайте, хотите ли вы включить или выключить XACT_ABORT. В любом случае будьте последовательны.
• Выход при первой ошибке - это реализует модель KISS.

• При выполнении хранимой процедуры всегда проверяйте @@error и возвращаемое значение. Например:

  EXEC @err = AnyStoredProc @value
  SET  @save_error = @@error
  -- NULLIF says that if @err is 0, this is the same as null
  -- COALESCE returns the first non-null value in its arguments
  SELECT @err = COALESCE( NULLIF(@err, 0), @save_error )
  IF @err <> 0 BEGIN 
      -- Because stored proc may have started a tran it didn't commit
      ROLLBACK TRANSACTION 
      RETURN @err 
  END
  

• При выполнении локальной хранимой процедуры, которая приводит к ошибке, выполните откат, поскольку процедура могла запустить транзакцию, которую она не зафиксировала, или откат.
• Не думайте, что только потому, что вы не начали транзакцию, нет активной транзакции - вызывающая сторона могла начать ее.
• В идеале, избегайте отката транзакции, которая была запущена вашим абонентом - проверьте @@trancount.
• Но в триггере всегда выполняйте откат, поскольку вы не знаете, инициировал ли вызывающая сторона активную транзакцию (потому что @@trancount всегда> = 1).

• Всегда сохраняйте и проверяйте @@error после следующих утверждений:

  INSERT, DELETE, UPDATE
  SELECT INTO
  Invocation of stored procedures
  invocation of dynamic SQL
  COMMIT TRANSACTION
  DECLARE and OPEN CURSOR
  FETCH from cursor
  WRITETEXT and UPDATETEXT
  

• Если произойдет сбой DECLARE CURSOR на глобальном курсоре процесса (по умолчанию), введите оператор для освобождения курсора.
• Будьте осторожны с ошибкой в UDF. Когда в UDF возникает ошибка, выполнение функции немедленно прерывается, как и запрос, вызвавший UDF, но @@error равен 0! В этих обстоятельствах вы можете захотеть работать с SET XACT_ABORT ON.
• Если вы хотите использовать динамический SQL, попробуйте использовать только один SELECT в каждом пакете, потому что @@error содержит только состояние последней выполненной команды. Наиболее вероятными ошибками в пакете динамического SQL являются синтаксические ошибки, и SET XACT_ABORT ON не учитывает их.

Ответ 2

Единственный трюк, который я всегда пытаюсь использовать: Всегда включайте пример использования в комментарии в верхней части. Это также полезно для тестирования вашего SP. Мне нравится включать наиболее распространенные примеры - тогда вам даже не требуется SQL-запрос или отдельный .sql файл с вашим любимым вызовом, поскольку он хранится прямо на сервере (это особенно полезно, если вы сохранили procs, которые смотрят на sp_who для блоков или любого другого и взять кучу параметров).

Что-то вроде:

/*
    Usage:
    EXEC usp_ThisProc @Param1 = 1, @Param2 = 2
*/

Затем, чтобы проверить или запустить SP, просто выделите этот раздел в script и выполните.

Ответ 3

Это очень общий вопрос, но вот несколько советов:

• Назовите свои хранимые процедуры последовательно. Многие используют префикс, чтобы идентифицировать его как хранимую процедуру, но не используют "sp_" в качестве префикса, как указано для базовых баз данных (в любом случае в SQL Server).
• Установите NOCOUNT, поскольку это уменьшает количество возможных возвращаемых значений
• Запросы, основанные на настройках, часто работают лучше, чем курсоры. Этот вопрос попадает в это гораздо подробнее.
• Если вы используете переменные DECLARE для вашей хранимой процедуры, используйте хорошие соглашения об именах так же, как и в любом другом программировании.
• Вызовите SP с использованием своего полностью квалифицированного имени, чтобы устранить любую путаницу в отношении того, какой СП должен быть вызван, и помочь повысить производительность SQL Server; это облегчает поиск рассматриваемого СП.

Там гораздо больше, конечно. Вот ссылка на больше: Советы по оптимизации хранимых процедур SQL Server

Ответ 4

• Всегда используйте SET NOCOUNT ON
• Если вы собираетесь выполнять две или более вставки/обновления/удаления, используйте транзакцию.
• Никогда не назовите ваш procs 'sp_'. Сначала SQL Server будет искать в основной базе данных, а не искать ее, а затем искать в своей базе данных вторую. Если вы будете называть свои procs по-разному, SQL Server будет выглядеть в вашей базе данных в первую очередь.

Плохо:

SET NOCOUNT ON
BEGIN TRAN
  INSERT...
  UPDATE...
COMMIT

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

SET NOCOUNT ON
BEGIN TRAN
  INSERT...
  IF @ErrorVar <> 0
  BEGIN
      RAISERROR(N'Message', 16, 1)
      GOTO QuitWithRollback
  END

  UPDATE...
  IF @ErrorVar <> 0
  BEGIN
      RAISERROR(N'Message', 16, 1)
      GOTO QuitWithRollback
  END

  EXECUTE @ReturnCode = some_proc @some_param = 123
  IF (@@ERROR <> 0 OR @ReturnCode <> 0)
       GOTO QuitWithRollback 
COMMIT
GOTO   EndSave              
QuitWithRollback:
    IF (@@TRANCOUNT > 0)
        ROLLBACK TRANSACTION 
EndSave:

Хорошо:

SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRY
    BEGIN TRAN
    INSERT...
    UPDATE...
    COMMIT
END TRY
BEGIN CATCH
    IF (XACT_STATE()) <> 0
        ROLLBACK
END CATCH

Лучший:

SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
    INSERT...
    UPDATE...
COMMIT

Итак, где обработка ошибок в "Лучшем" решении? Вам не нужны. См. SET XACT_ABORT ON, что означает автоматический откат при возникновении ошибок. Код чище и проще в чтении, проще писать и менее багги. Меньше багги, потому что нет возможности упустить ошибку, поскольку SQL Server теперь делает это для вас.

Ответ 5

В SQL Server я всегда ставил инструкцию, которая отбросит процедуру, если она существует, поэтому я могу легко удалять повторное создание процедуры, пока я ее разрабатываю. Что-то вроде:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'usp') AND type in (N'P', N'PC'))
DROP PROCEDURE usp

Ответ 6

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

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

Если вам нужна проверка записи в proc, чтобы убедиться, что ваш конечный результат будет правильным. Я специалист по ETL, и я всегда пишу свои procs, чтобы очистить и нормализовать данные, прежде чем пытаться импортировать их в свои таблицы. Если вы делаете что-то из пользовательского интерфейса, это может быть не так важно делать в proc, хотя я бы хотел, чтобы пользовательский интерфейс выполнял проверку перед тем, как даже запустить proc, чтобы данные были хорошими для вставки (например, проверка, чтобы убедиться поле даты содержит реальную дату, что все обязательные поля имеют значения и т.д.)

Если вы пишете procs для размещения больших объемов данных в таблицах, лучше всего иметь способ проверить эти результаты до их завершения. Вы будете поражены нежелательным результатом, который вы получите от клиентов и поставщиков для импорта данных. Мы пишем все наши процессы импорта с помощью тестового флага. Таким образом, вы можете вернуть выбранные данные, а не выполнять действие, чтобы вы могли видеть заранее, что именно вы повлияли бы.

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

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

Если вы используете аргументы case или if, убедитесь, что вы сделали тестирование, которое ударит по каждой возможной ветке. Тот, который вы не тестируете, - это тот, который не сработает.

Ответ 7

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

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

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

Ответ 8

С SQL Server 2008 используйте конструкцию TRY... CATCH, которую вы можете использовать в своих хранимых процедурах T-SQL, чтобы обеспечить более изящный механизм обработки исключений, чем это было в предыдущих версиях SQL Server, путем проверки @@ERROR (и часто использование операторов GOTO) после каждого оператора SQL.

         BEGIN TRY
             one_or_more_sql_statements
         END TRY
         BEGIN CATCH
             one_or_more_sql_statements
         END CATCH

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

         ERROR_NUMBER()
         ERROR_MESSAGE()
         ERROR_SEVERITY()
         ERROR_STATE()
         ERROR_LINE()
         ERROR_PROCEDURE()

В отличие от ошибки @@, которая составляет reset для каждого выполняемого оператора, информация об ошибке, полученная функциями ошибки, остается постоянной в пределах области действия блока CATCH инструкции TRY... CATCH. Эти функции могут позволить модуляции обработки ошибок в одну процедуру, поэтому вам не нужно повторять код обработки ошибок в каждом блоке CATCH.

Ответ 9

Основные вещи:

Имейте политику обработки ошибок и ошибки ловушки во всех операторах SQL.
Определите политику использования контроля исходного кода для хранимых процедур.
Включите прокомментированный заголовок с пользователем, дату/время и цель sp.
Явно возвращаю 0 (успех) для успешного выполнения, что-то еще в противном случае.
Для нетривиальных процедур включите тестовый пример (или случаи) и описание ожидаемого результата.
Получите привычку к тестированию производительности. Для текстовых случаев записывайте время выполнения как минимум.
Понять явные транзакции и использовать их.
Почти никогда не вызывайте SP из SP. Повторное использование - это другая игра с SQL.

Ответ 10

Ниже приведены некоторые лучшие практики,

1. Избегайте префикса _sp
2. Включить инструкцию SET NOCOUNT ON
3. Старайтесь не использовать временную таблицу
4. Старайтесь избегать использования Select * from
5. Старайтесь избегать использования курсора
6. используйте правильную индексацию
7. Правильная обработка ошибок

Для получения дополнительных объяснений и примеров кода T-SQL, пожалуйста, проверьте этот пост

Ответ 11

Вот некоторый код, доказывающий, что на SQL Server нет многоуровневых ROLLBACK, и он иллюстрирует, как обрабатываются транзакции:

BEGIN TRAN;

    SELECT @@TRANCOUNT AS after_1_begin;

BEGIN TRAN;

    SELECT @@TRANCOUNT AS after_2_begin;

COMMIT TRAN;

    SELECT @@TRANCOUNT AS after_1_commit;

BEGIN TRANSACTION;

    SELECT @@TRANCOUNT AS after_3_begin;

ROLLBACK TRAN;

    SELECT @@TRANCOUNT AS after_rollback;