Хранимая процедура - возвращает идентификационные данные как выходной параметр или скаляр
При вставке записи в таблицу со столбцом идентификационных данных можно использовать SCOPE_IDENTITY () для получения того значения. В контексте хранимой процедуры, которая была бы рекомендуемым способом возвратить значение идентификационных данных:
- Как выходной параметр
SET @RETURN_VALUE = SCOPE_IDENTITY() - Как скаляр
SELECT SCOPE_IDENTITY() - Иначе?
Какие-либо профессионалы/недостатки каждому?
4 ответа
Все зависит от уровня доступа к данным вашего клиента. Многие структуры ORM полагаются на явный запрос SCOPE_IDENTITY во время операции вставки.
Если вы полностью контролируете уровень доступа к данным, возможно, лучше вернуть SCOPE_IDENTITY () в качестве выходного параметра. Заключение возврата в набор результатов добавляет ненужные накладные расходы на метаданные для описания набора результатов и усложняет код для обработки результата запроса.
Если вы предпочитаете возвращать набор результатов, то, опять же, возможно, лучше использовать предложение OUTPUT:
INSERT INTO MyTable (col1, col2, col3)
OUTPUT INSERTED.id, col1, col2, col3
VALUES (@col1, @col2, @col3);
Таким образом вы можете вернуть всю вставленную строку назад, включая столбцы по умолчанию и вычисленные столбцы, и вы получить набор результатов, содержащий одну строку для каждой вставленной строки, это правильно работает с пакетными вставками, ориентированными на набор.
В целом, я не вижу ни одного случая, когда возвращение SCOPE_IDENTITY () в качестве набора результатов было бы хорошей практикой.
Другой вариант - это возвращаемое значение для хранимой процедуры (я не предлагаю этого, поскольку обычно это лучше всего для значений ошибок).
Я включил его как при вставке одной строки в случаях, когда хранимая процедура использовалась как другими процедурами SQL, так и внешним интерфейсом, который не мог работать с параметрами OUTPUT (IBATIS в .NET, я считаю ):
CREATE PROCEDURE My_Insert
@col1 VARCHAR(20),
@new_identity INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO My_Table (col1)
VALUES (@col1)
SELECT @new_identity = SCOPE_IDENTITY()
SELECT @new_identity AS id
RETURN
END
С выходным параметром легче работать в T-SQL при вызове из других хранимых процедур IMO, но некоторые языки программирования плохо поддерживают или не поддерживают выходные параметры и лучше работают с наборами результатов.
Я предпочитаю возвращать значение идентичности в качестве выходного параметра. Результат SP должен указывать, удалось это или нет. Значение 0 указывает на успешное завершение SP, ненулевое значение указывает на ошибку. Кроме того, если вам когда-либо понадобится внести изменение и вернуть дополнительное значение из SP, вам не нужно вносить никаких изменений, кроме добавления дополнительного выходного параметра.
Либо как набор записей, либо как выходной параметр. У последнего меньше накладных расходов, и я бы предпочел использовать его, а не набор записей из одного столбца / строки.
Если бы я ожидал> 1 строки, я бы использовал предложение OUTPUT и набор записей
Обычно для обработки ошибок использовались бы возвращаемые значения.