Получение автоувеличенного значения столбца от таблицы, где несколько вставляют/выбирают в единственную хранимую процедуру
Однажды я услышал, как кто-то сказал, что если метод / функция становится слишком большим, чтобы уместиться на одном экране без прокрутки, то его следует преобразовать в отдельные методы.
Это не всегда верно и нет смысла в рефракторинге для рефакторинга, но часто помогает держать вещи в перспективе.
6 ответов
используйте scope_identity () после вставки, чтобы захватить самое последнее единичное значение идентификатора из текущей области:
DECLARE @ID int
INSERT ......
SELECT @ID=scope_identity()
используйте @ID везде, где вам это нужно
примечание: SCOPE_IDENTITY () - это предпочтительнее, чем старый @@ IDENTITY, поскольку он дает последнее значение идентификатора в текущей области, что позволяет избежать проблем с триггерами, которые вставляются в таблицы журнала (с идентификаторами).
Однако, если вам нужно несколько значений идентификаторов (вставка набора строк), используйте OUTPUT и INTO:
declare @test table (RowID int identity(1,1) primary key not null, RowValue varchar(10) null)
declare @OutputTable table (RowID int not null)
insert into @test (RowValue)
OUTPUT INSERTED.RowID
INTO @OutputTable
SELECT 'A'
UNION SELECT 'B'
UNION SELECT 'C'
UNION SELECT 'D'
UNION SELECT 'E'
select * from @OutputTable
вывод:
(5 row(s) affected)
RowID
-----------
1
2
3
4
5
(5 row(s) affected)
для MS Sql Server
Всякий раз, когда вы вставляете запись и в ней есть столбец с автоинкрементом (столбец идентификаторов на языке ms-sql), вы можете использовать его, чтобы получить идентификатор строки, которую вы вставлено:
@id = SCOPE_IDENTITY()
это гарантирует, что вы получите значение столбца идентификатора, созданное вашей вставкой, а не другими пользователями во время выполнения вашего кода (между вставкой и получением идентификатора с помощью scope_identity () )
ТАКЖЕ не фиксируйте в середине транзакции, вы хотите, чтобы обе вставки откатывались в случае сбоя одной. Читайте в Интернет-книгах об обработке транзакций.
Вы можете использовать глобальную переменную @@ Identity для получения последнего вставленного значения идентификатора
SELECT @ManagerId=@@Identity
, вы также можете использовать функции Scope_Identity и IDENT_Current
Уф ... у вас перепутаны процедура и транзакция ...
Вы хотите, чтобы эти две вставки произошли внутри той же транзакции (которую я получаю из оператора COMMIT в середине вашей исходной процедуры). Поэтому вам необходимо соответствующим образом разместить операторы BEGIN TRANSACTION и COMMIT вокруг всех ваших операторов INSERT и SELECT , чтобы изолировать изменение данных.
Примите во внимание следующее:
CREATE PROCEDURE [dbo].[sptInsertNewManager]
-- Add the parameters for the stored procedure here
@FName varchar(50),
@LName varchar(50),
@EMail varchar(100),
@UserRoleID int,
@LANUserID varchar(25),
@GroupID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @ManagerID INT
BEGIN TRANSACTION
-- Insert statements for procedure here
INSERT INTO [Manager]
([FName],
[LName],
[Email],
[UserRoleID],
[LANUserID],
[ActiveFlag],
[GroupID]
)
VALUES
(@FName
,@LName
,@EMail
,@UserRoleID
,@LANUserID
,1
,@GroupID);
-- Collect the ID you just created
SELECT @ManagerID = SCOPE_IDENTITY()
--also insert into Users table.
INSERT INTO [dbo].[aspnet_Users] (
[UserId],
[UserName],
[LoweredUserName],
[ManagerId]
)
VALUES (
NEWID(),
@LANUserID,
LOWER(@LANUserID),
@ManagerID) -- This is the new identity you just created
COMMIT
END
Мы можем вернуть колонку с идентификатором как:
SELECT @@identity as 'Identity'
и прочитать это значение в коде как:
int ID = Convert.ToInt32(cmdinsert.ExecuteScalar());