冒险作品2019:添加新的人到人.人表

Question

现在我使用3个存储过程将一个人添加到这个人。人表。我想把它简化成一个存储过程来解决这个问题。

1. 插入新的GUID和DateModified到表Person。BusinessEntity
2. 选择自动生成BusinessEntityID表单表Person。BusinessEntity
3. 插入新的Person到Person。人表

存储过程都使用我通过c#应用程序传递的参数，并且我已经确认用户实际上已添加到AdventureWorks2019 Db。

1. 过程:人。CreateNewBusinessEntity

   INSERT INTO [Person].[BusinessEntity]
   (
   [BusinessEntity].rowguid
   , [BusinessEntity].ModifiedDate
   )
   VALUES
   (
   @RowGUID
   , GetDate()
   )
   

2. 过程:人。GetBusinessEntityID

   SELECT 
   [BusinessEntityID]
   FROM 
   [AdventureWorks2019].[Person].[BusinessEntity]
   WHERE  [rowguid] = @RowGuid
   

3. 过程:人。CreateNewPerson

   INSERT INTO [Person].[Person]
   (
   [BusinessEntityID]
   ,[PersonType]
   ,[NameStyle]
   ,[Title]
   ,[FirstName]
   ,[MiddleName]
   ,[LastName]
   ,[Suffix]
   ,[EmailPromotion]
   ,[AdditionalContactInfo]
   ,[Demographics]
   ,[rowguid]
   ,[ModifiedDate]
   )
   VALUES
   (
   @BusinessEntityID
   , @PersonType
   , @NameStyle
   , @Title
   , @FirstName
   , @MiddleName
   , @LastName
   , @Suffix
   , @EmailPromotion
   , @AdditionalContactInfo
   , @Demographics
   , @RowGUID
   , GetDate()
   )
   

感谢您的帮助。谢谢!

感谢HABO，我现在正在使用这个解决方案。现在我只需要两个程序。

DECLARE @Inserted table ( [BusinessEntityID] int );

INSERT INTO [Person].[BusinessEntity]
(
[BusinessEntity].rowguid
, [BusinessEntity].ModifiedDate
)
OUTPUT inserted.[BusinessEntityID] INTO @Inserted([BusinessEntityID])
VALUES
(
@RowGUID
, GetDate()
)

Answer 1

下面的答案向您展示了如何获取插入的id并将其添加到下一个插入的相同SP。

USE AdventureWorks2012
GO
CREATE PROC  CreateNewPerson 
AS 
BEGIN

DECLARE @OutputTbl TABLE ([BusinessEntityID] INT, ModifiedDate DATETIME)
DECLARE @BusinessEntityID AS INT  
INSERT INTO [Person].[BusinessEntity]
(
[BusinessEntity].rowguid 
, [BusinessEntity].ModifiedDate
)
--Get the output value inserted to a table. 
OUTPUT inserted.[BusinessEntityID], inserted.ModifiedDate  INTO 
@OutputTbl([BusinessEntityID],[ModifiedDate])
VALUES
(
NEWID()
, GetDate()
)

--Assigned to a variable. You can get this using subquery as well inside the insert statment. 
SELECT @BusinessEntityID =  [BusinessEntityID] FROM  @OutputTbl
INSERT INTO [Person].[Person]
(
[BusinessEntityID]
,[PersonType]
,[NameStyle]
,[Title]
,[FirstName]
,[MiddleName]
,[LastName]
,[Suffix]
,[EmailPromotion]
,[AdditionalContactInfo]
,[Demographics]
,[rowguid]
,[ModifiedDate]
)
VALUES
(
@BusinessEntityID
, @PersonType --These columns with @ sign needed to be declared or supply values 
, @NameStyle
, @Title
, @FirstName
, @MiddleName
, @LastName
, @Suffix
, @EmailPromotion
, @AdditionalContactInfo
, @Demographics
, NEWID() --This will generate a new GUID for each row.
, GetDate()
)
END
GO

第二种方法是使用Scope_identity()。SELECT @BusinessEntityID = [BusinessEntityID] FROM @OutputTbl'

SELECT @BusinessEntityID =  SCOPE_IDENTITY() --[BusinessEntityID] FROM  @OutputTbl
Select @BusinessEntityID