问题描述:

I am creating a stored procedure to create a new customer so for instance,

CREATE PROCEDURE Customer_Create

@customer_arg

@type_arg

AS

BEGIN

INSERT INTO Customer (Customer_id, Type_id)

VALUES (@Customer_arg,@type_arg)

End;

If I have several foreign keys in my statement and they are all ID's is there a way for me to pull the NEXT ID number automatically without having to know what it would be off the top of my head when I run the execute statement? I would like to just have it pull the fact that the ID will be 2 because the previous record was 1

EXECUTE Customer_Create 16,2

Is it something wnith output? If so how does this work code wise

网友答案:

I suspect that what you want to do is return the new id after the record is inserted. For that:

CREATE PROCEDURE Customer_Create (
    @customer_arg,
    @type_arg,
    @NewCustomerId int output
) AS
BEGIN
    INSERT INTO Customer(Customer_id, Type_id)
        VALUES (@Customer_arg, @type_arg);

    @NewCustomerId = scope_identity();
End;

There are several other choices for getting the identity, which are explained here.

网友答案:

To get to the last inserted IDENTITY value you should use the OUTPUT clause like this:

DECLARE @IdentValues TABLE(v INT);

INSERT INTO dbo.IdentityTest 
OUTPUT INSERTED.id INTO @IdentValues(v)
DEFAULT VALUES;

SELECT v AS IdentityValues FROM @IdentValues;

There are several other mechanisms like @@IDENTITY but they all have significant problems. See my Identity Crisis article for details.

网友答案:

In your case you can also experiment with @IDENTITY like this

DECLARE @NextID int
--insert statement goes here
SET @NextID = @@Identity`

Here are couple good resources for getting familiar with this

http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

http://blog.sqlauthority.com/2013/03/26/sql-server-identity-fields-review-sql-queries-2012-joes-2-pros-volume-2-the-sql-query-techniques-tutorial-for-sql-server-2012/

相关阅读:
Top