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

30
задан blntechie 26 May 2010 в 07:12
поделиться

8 ответов

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

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

    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).
  • Всегда хранилище и проверка @@ ошибка после следующих утверждений:

    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 - но @@ ошибка 0! Можно хотеть выполнить с НАБОРОМ XACT_ABORT НА при этих обстоятельствах.
  • , Если Вы хотите использовать динамический SQL, попытайтесь иметь только единственный ВЫБОР в каждом пакете, потому что @@ ошибка только содержит состояние последней выполняемой команды. Наиболее вероятные ошибки от пакета динамического SQL являются синтаксическими ошибками, и они не заботятся о НАБОРОМ XACT_ABORT НА.
40
ответ дан Community 27 November 2019 в 23:13
поделиться

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

Что-то как:

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

Затем, чтобы протестировать или выполнить SP, Вы просто выделяете тот раздел в своем сценарии и выполняетесь.

18
ответ дан Cade Roux 27 November 2019 в 23:13
поделиться

Это - очень общий вопрос, но здесь является несколькими советами:

  • Имя Ваши хранимые процедуры последовательно. Многие используют префикс, чтобы определить, что это - хранимая процедура, но не используйте 'SP _' в качестве префикса, поскольку это определяется для Основного databae (в SQL Server так или иначе)
  • Набор NOCOUNT на, поскольку это сокращает количество возможных возвращаемых значений
  • , Основанные на наборе запросы часто работают лучше, чем курсоры. Этот вопрос входит в это в намного большем количестве деталей.
  • , Если Вы - переменные DECLARE'ing для своей хранимой процедуры, используйте хорошие соглашения о присвоении имен так же, как Вы/должны в любом другом виде программирования.
  • SPS Вызова использование их полностью определенного имени для устранения любого беспорядка, о котором SP нужно назвать, и помочь повысить производительность SQL Server; это облегчает находить рассматриваемый SP.

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

11
ответ дан Community 27 November 2019 в 23:13
поделиться

В 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
3
ответ дан Scott 27 November 2019 в 23:13
поделиться

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

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

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

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

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

не делают вещей в курсоре, просто becasue Вы хотят снова использовать, другой сохранил proc, который только работает над одной записью во время. Повторное использование кода, которое вызывает проблемы производительности если плохая вещь.

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

2
ответ дан HLGEM 27 November 2019 в 23:13
поделиться

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

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

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

1
ответ дан Mitchel Sellers 27 November 2019 в 23:13
поделиться

Основной материал:

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

0
ответ дан dkretz 27 November 2019 в 23:13
поделиться
  1. Всегда используйте SET NOCOUNT ON
  2. Если вы собираетесь выполнить две или более вставки / обновления / удаления, пожалуйста, используйте транзакцию.
  3. Никогда не называйте свои процессы 'sp_'. SQL Server сначала будет искать в базе данных master, а не находить ее, а затем искать в вашей базе данных. Если вы назовете свои процессы по-другому, 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 теперь делает это за вас.

11
ответ дан 27 November 2019 в 23:13
поделиться
Другие вопросы по тегам:

Похожие вопросы: