Вот мои инструкции обработки ошибок хранимой процедуры.
При выполнении хранимой процедуры, всегда проверяйте и @@ ошибка и возвращаемое значение. Например:
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
Всегда хранилище и проверка @@ ошибка после следующих утверждений:
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
Единственный прием, который я всегда пытаюсь использовать: Всегда включайте использование в качестве примера в комментарий около вершины. Это также полезно для тестирования Вашего SP. Мне нравится включать наиболее распространенные примеры - тогда Вам даже не нужны Подсказка SQL или отдельный .sql файл с Вашим любимым вызовом, так как это хранится тут же в сервере (это expecially полезно при хранении procs, которые смотрят на вывод sp_who для блоков или безотносительно и берут набор параметров).
Что-то как:
/*
Usage:
EXEC usp_ThisProc @Param1 = 1, @Param2 = 2
*/
Затем, чтобы протестировать или выполнить SP, Вы просто выделяете тот раздел в своем сценарии и выполняетесь.
Это - очень общий вопрос, но здесь является несколькими советами:
существует намного больше, конечно. Вот, ссылка с больше: Подсказки по Оптимизации Хранимых процедур SQL Server
В 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
Это зависит значительно от того, что Вы делаете в сохраненном procs. Однако это - хорошая идея использовать транзакции, если Вы делаете, несколько вставляют/обновляют или удаляют в одном proc. Тот путь, если одна часть перестала работать, другие части, откатывается, оставляя Вашу базу данных в согласованном состоянии.
двумя самыми важными вещами рассмотреть при записи в базу данных (и таким образом при использовании сохраненного proc, который выполняет действие кроме выбора) является целостность данных и производительность. Без целостности данных у Вас просто есть база данных, которая содержит мусор и бесполезна. Без производительности у Вас не будет пользователей (если они будут внешними клиентами), или несчастные пользователи (если они получают мандат использовать Ваш продукт, обычно внутренние пользователи, у которых нет выбора пойти в другое место). Никакой не хорош для Вашей карьеры. Таким образом в записи сохраненного proc, удостоверьтесь, что Вы сначала удостоверяетесь, что данные будут вводиться правильно в базу данных и что это перестанет работать, если будет проблема в одной части действия.
В случае необходимости запись зарегистрировалась в proc, чтобы гарантировать, что Ваш конечный результат будет корректен. Я - специалист ETL, и я всегда пишу, что мой procs для создания данных убран и нормализован, прежде чем я попытаюсь импортировать его в свои таблицы. При выполнении вещей от пользовательского интерфейса, это не могло бы быть настолько важно, чтобы сделать inthe proc, хотя я сделаю, чтобы пользователь взаимодействовал через интерфейс, делают проверки прежде даже выполнить proc, чтобы гарантировать, что данные хороши для вставки (вещи как проверка, чтобы удостовериться, что datefield содержит реальную дату, что все обязательные поля имеют значения, и т.д.)
, Если Вы пишете procs для помещения больших объемов данных в таблицы, лучше иметь способ протестировать те результаты, прежде чем они будут завершены. Вы были бы поражены спамом, который Вы получите от клиентов и поставщиков для импорта данных. Мы пишем весь наш импорт procs с тестовым флагом. Тем путем можно возвратить избранные данные, а не выполнить действие, так, чтобы Вы видели заранее, точно на что Вы влияли бы.
я не поклонник динамического SQL, и я предпочитаю не использовать его в сохраненном procs. Если Вы застреваете с динамическим SQL в существующем procs, вставьте флаг отладки, который позволит Вам печатать SQL, а не выполнять его. Тогда вставьте комментарии самые типичные случаи, которые необходимо будет выполнить. Вы найдете, что можно поддержать proc намного лучше, если Вы делаете это.
не делают вещей в курсоре, просто becasue Вы хотят снова использовать, другой сохранил proc, который только работает над одной записью во время. Повторное использование кода, которое вызывает проблемы производительности если плохая вещь.
при использовании операторов выбора или если операторы, удостоверьтесь, что Вы сделали тестирование, которое поразит каждое возможное ответвление. Тот, который Вы не тестируете, является тем, который перестанет работать.
Это не вопрос, на который можно ответить непосредственно без большей информации, но несколько общих правил ползунка действительно применяются.
Хранимые процедуры являются просто запросами T-SQL, которые хранятся. Поэтому становление более знакомым с T-SQL и различными функциями и синтаксисами действительно, что необходимо сделать. И еще больше с точки зрения производительности необходимо будет удостовериться, чтобы запросы и базовые структуры данных соответствовали способом, которые допускают хорошую производительность. IE, удостоверьтесь, что индексы, отношения, ограничения и т.д. реализованы при необходимости.
Понимание, как использовать настраивающие инструменты производительности, understaning, как планы выполнения работают, и вещи той природы, то, как Вы добираетесь до того "следующего уровня"
Основной материал:
Имеют политику обработки ошибок и захватывают ошибки на всех SQL-операторах.
Выбирают политику для использования управления исходным кодом для хранимых процедур.
Включают прокомментированный заголовок с пользователем, датой/временем и целью SP
Явно возврат 0 (успех) для успешного выполнения, что-то еще иначе.
Для нетривиальных процедур, включайте тестовый сценарий (или случаи) и описание ожидаемого результата.
Привыкают к тестированию производительности. Для текстовых случаев запишите время выполнения, по крайней мере.
Понимают явные транзакции и используют их.
Почти никогда SPS вызова от сверхзвуковой Возможности многократного использования не является различным ballgame с SQL.
Плохо:
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 теперь делает это за вас.