CODEWITHSUNDEEP
stored-procedures
Adithya Sai
Adithya Sai

Reputation: 1740

Stored procedure that insert a row and get the id of it and insert that id into another table

I have a table user with columns

Id | Name | Email

I have another table employee with columns

Id | UserId | Designation

Is there a way to write a stored procedure where I insert a row into User and get the Id, and then use that Id (as UserId) to insert a row into the Employee table?

Upvotes: 1

Views: 2837

Answers (1)

marc_s
marc_s

Reputation: 754398

Of course - no problem - try code something like this:

CREATE PROCEDURE dbo.InsertUserAndEmployee 
    (@Name VARCHAR(100), 
     @Email VARCHAR(255), 
     @Designation VARCHAR(100)
    )
AS 
BEGIN
    -- Insert into "User"
    INSERT INTO dbo.User (Name, Email)
    VALUES (@Name, @Email);

    -- get the newly inserted ID
    DECLARE @UserId INT;
    SELECT @UserId = SCOPE_IDENTITY();

    -- Insert into "Employee"
    INSERT INTO dbo.Employee (UserId, Designation)
    VALUES (@UserId, @Designation);
END

Upvotes: 4

Related Questions