How do I get a stored procedure to insert @@identity of previous inserts AUTO ID

Question

Image above is order details table and the orderID is foreign key ref orders table.

This is my orders table which will generate a id when a table is inserted via this stored procedure below. PK OrderID

The stored procedure that inserts a table will happen first which will generate a orderID. I will then run this stored procedure below wanting to insert the last inserted orderID from orders into Orderdetails table using @@identity

but when I try to insert it comes up with this error saying the orderID is null

Have I written a wrong SQL statement to perform my desired action of inserting previously generated ID from one table to another

Welcome to Stack Overflow, Cdob. Consider providing more information without external links for images and some code so people can know how to help you. For more information, take a look at [How To Ask](https://stackoverflow.com/help/how-to-ask). — Igor Escodro, Dec 06 '19 at 16:31
I haven't read/looked at your images, as they are unlikely to help. If you do want to supply code or data, do so as `text`, so that we can consume the information. I suggest, however, looking at the `OUTPUT` clause. — Thom A, Dec 06 '19 at 16:33
You might also want to avoid using @@identity. See this question: https://stackoverflow.com/questions/1920558/what-is-the-difference-between-scope-identity-identity-identity-and-ide — Eric Humphrey, Dec 06 '19 at 16:50
I've voted to close this topic as the OP is expecting us to transcribe their code to help them. We are therefore unable to replicate their problem. — Thom A, Dec 06 '19 at 16:55

score 1 · Answer 1 · answered Dec 06 '19 at 17:48

1

Remove line 13 from testinsert666. You're passing in the OrderID, so you don't need to assign it there. Any call to @@identity ( or preferably scope_identity()) would be in `testinsert333'.

answered Dec 06 '19 at 17:48

David Browne - Microsoft

80,331
6
39
67

score 1 · Accepted Answer · answered Dec 06 '19 at 18:01

You're missing a few things. First of all, you can't invoke @@IDENTITY. It's purpose is to save the identity value of a row you just inserted. Also, it's got issues with scoping, so you should use SCOPE_IDENTITY instead.

Your first procedure needs to return the inserted identity to the outer wrapper. This can be accomplished by using an OUTPUT parameter.

CREATE PROCEDURE dbo.testinsert333
(
    @TableID TINYINT
    ,@OrderID   INT OUTPUT
)
AS
BEGIN
    INSERT INTO dbo.Orders (Orders.TableID)
    VALUES (@TableID)
    SET @OrderID = SCOPE_IDENTITY()
END

Your second procedure needs to just accept @OrderID. There's no way for this thread to snag an inserted identity value from a different thread. It must come in as a parameter.

CREATE PROCEDURE dbo.testinsert666
(
    @OrderID    INT
    ,@ProductID INT
    ,@Price INT
    ,@Quantity SMALLINT
    ,@OrderStatus BIT
)
AS
BEGIN
    INSERT INTO dbo.OrderDetails
                (
                    OrderID
                    ,ProductID
                    ,Price
                    ,Quantity
                    ,OrderStatus
                )
    SELECT  @OrderID
            ,@ProductID
            ,@Price
            ,@Quantity
            ,@OrderStatus
END
GO

Now to tie all this together, they need to be called together, as in the example below.

BEGIN
    DECLARE @OrderID INT
    EXEC dbo.testinsert333 @TableID = 1, @OrderID = @OrderID OUTPUT

    EXEC dbo.testinsert666
            @OrderID = @OrderID
            ,@ProductID = @ProductID
            ,@Price = @Price
            ,@Quantity = @Quantity
            ,@OrderStatus = @OrderStatus
END

However, the best approach is to nest these two procedures into one, like below.

CREATE dbo.usp_AllInWonder
(
    @TableID INT
    ,@ProductID INT
    ,@Price INT
    ,@Quantity SMALLINT
    ,@OrderStatus BIT
)
AS
BEGIN

    DECLARE @OrderID INT

    INSERT INTO dbo.Orders (Orders.TableID)
    VALUES (@TableID)

    SET @OrderID = SCOPE_IDENTITY()

    INSERT INTO dbo.OrderDetails
        (
            OrderID
            ,ProductID
            ,Price
            ,Quantity
            ,OrderStatus
        )
    SELECT  @OrderID
            ,@ProductID
            ,@Price
            ,@Quantity
            ,@OrderStatus
END

score 0 · Answer 3 · answered Dec 06 '19 at 16:22

0

For you to use the @@IDENTITY in testinsert666, you need you have the insert creating @@IDENTITY to happen in the procedure testinsert666. testinsert666 can only be used in the same session it was generated. See @@IDENTITY (Transact-SQL)

answered Dec 06 '19 at 16:22

zip

3,938
2
11
19

How do I get a stored procedure to insert @@identity of previous inserts AUTO ID

3 Answers3