Сохраненная процедура - возвращает идентификатор в качестве выходного параметра или скалярного

Когда вы вставляете запись в таблицу со столбцом идентификатора, вы можете использовать SCOPE_IDENTITY(), чтобы получить это значение. В контексте хранимой процедуры, которая будет рекомендуемым способом вернуть значение идентификатора:

  • В качестве выходного параметра SET @RETURN_VALUE = SCOPE_IDENTITY()
  • Как скаляр SELECT SCOPE_IDENTITY()
  • Другой способ?

Любые плюсы и минусы для каждого?

Ответы

Ответ 1

Все зависит от вашего уровня доступа к данным клиента. Многие рамки ORM полагаются на явный запрос SCOPE_IDENTITY во время операции вставки.

Если вы полностью контролируете уровень доступа к данным, возможно, лучше вернуть SCOPE_IDENTITY() в качестве выходного параметра. Обертка возврата в результирующем наборе добавляет лишние метаданные для описания набора результатов и усложняет код для обработки результата запроса.

Если вы предпочитаете возвращать результат, то снова можно лучше использовать предложение OUTPUT:

INSERT INTO  MyTable (col1, col2, col3)
OUTPUT INSERTED.id, col1, col2, col3
VALUES (@col1, @col2, @col3);

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

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

Ответ 2

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

Я включил его как в том случае, когда он вставлял одну строку в случаях, когда хранимая процедура использовалась как другими процедурами 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, но некоторые языки программирования имеют плохую или не поддерживают выходные параметры и работают лучше с наборами результатов.

Ответ 3

Я предпочитаю возвращать значение идентификатора в качестве выходного параметра. Результат SP должен указывать, удалось ли это или нет. Значение 0 указывает, что SP успешно завершен, ненулевое значение указывает на ошибку. Кроме того, если вам когда-либо понадобится внести изменения и вернуть дополнительное значение из SP, вам не нужно вносить какие-либо изменения, кроме добавления дополнительного выходного параметра.

Ответ 4

Либо как набор записей, либо выходной параметр. У последнего меньше накладных расходов, и я хотел бы использовать это, а не один набор записей столбцов/строк.

Если бы я ожидал > 1 строку, я бы использовал предложение OUTPUT и набор записей

Возвращаемые значения обычно используются для обработки ошибок.

Ответ 5

SELECT IDENT_CURRENT('databasename.dbo.tablename') AS your identity column;