在存储过程范围身份 [英] scope identity in stored procedure

问题描述

我有以下存储过程。

ALTER PROCEDURE dbo.insert_movie

(
@first_name varchar(50),
@movie_name varchar(50),
@date date,
@last_id_inserted int,
@last_name varchar(50)
)

AS
INSERT INTO movies (movie_name,movie_date) VALUES (@movie_name,@date);

SELECT SCOPE_IDENTITY() AS scope;

INSERT INTO customer (first_name,last_name,movie_id) 
    VALUES (@first_name,@last_name,@last_id_inserted)

我要的范围变量插入@last_id_inserted。

I want to insert the scope variable into @last_id_inserted.

基本上，我想插入最后插入的ID（movie_id）的客户表。

Basically, I want to insert the last inserted id (movie_id) to the customer table..

不知道的语法虽然

推荐答案

您可以指定函数变量的结果。

You can assign the result of the function to the variable.

SET @last_id_inserted = SCOPE_IDENTITY();

有没有必要有 @last_id_inserted 作为参数。声明其作为SP的变量。

There is no need to have @last_id_inserted as a parameter. Declare it as a variable in the SP.

DECLARE @last_id_inserted int;
SET @last_id_inserted = SCOPE_IDENTITY();

或者你可以在INSERT语句中直接使用它。

Or you can use it directly in the insert statement.

INSERT INTO customer (first_name,last_name,movie_id) 
    VALUES (@first_name,@last_name,SCOPE_IDENTITY())

这篇关于在存储过程范围身份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！