Stored Procedure With Input Output Scope Identity

Question

I have two tables namely User and UserRole, where I want to pass the values via stored procedures with below tables. I need help how I can create a procedure which inserts into both the tables assuming a user has only one role i.e either User or Admin.

The Id parameter inserted into second table must be the Id of User Table. Please suggest me.

CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
    [Username] [nvarchar](50) NULL,
    [Password] [nvarchar](50) NULL,
)
CREATE TABLE [dbo].[UserRole](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [int] Foreign Key References User(ID)NOT NULL,
    [Role] [nvarchar](50) NULL
)

CREATE PROCEDURE [dbo].[AddUserRole]
    @Name      VARCHAR(50),
    @Username DATETIME,
    @Password      INT,
    @Role NVARCHAR(50),
     @Id INT OUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [WH].[dbo].[User]
                (
                Name,
                Username,
                Password
                )
    VALUES  
                (
                @ Name, 
                @Username,
                 @Password
                );

    SET @ Id = SCOPE_IDENTITY();

INSERT INTO [WH].[dbo].[UserRole]
                (
                UserId,
                Role
                )
    VALUES  
                (
                @Id, 
                @ Role
                );
END;

Answer 1

Now this working fine !

        CREATE TABLE [dbo].[TempUser](
        [Id] [int] IDENTITY(1,1) NOT NULL Primary key,
        [Name] [nvarchar](50) NULL,
        [Username] [nvarchar](50) NULL,
        [Password] [nvarchar](50) NULL,
    )
    CREATE TABLE [dbo].[TempUserRole](
        [Id] [int] IDENTITY(1,1) NOT NULL,
        [UserId] [int] Foreign Key References TempUser(ID)NOT NULL,
        [Role] [nvarchar](50) NULL
    )
    --ALTER TABLE TempAddUserRole DROP CONSTRAINT FK__TempUserR__UserI__503293D2;



EXEC TempAddUserRole 'Gulshan','12 sep 2018','1','Admin'
EXEC TempAddUserRole 'Gulshan','12 sep 2018','1','Admin',


ALTER PROCEDURE [dbo].[TempAddUserRole]
    @Name      VARCHAR(50),
    @Username DATETIME,
    @Password      INT,
    @Role NVARCHAR(50)

AS
BEGIN
    SET NOCOUNT ON;

     declare @Id INT 


    INSERT INTO [TempUser]
                (
                Name,
                Username,
                Password
                )
    VALUES  
                (
                @Name, 
                @Username,
                 @Password
                );

    SET @Id = SCOPE_IDENTITY();

INSERT INTO [TempUserRole]
                (
                UserId,
                Role
                )
    VALUES  
                (
                @Id, 
                @Role
                );
END;

Answer 2

Use output method, detail here

try Something like this

DECLARE @MyTableVar table( Id int);  

INSERT INTO [WH].[dbo].[User] (Name, Username, Password)
OUTPUT INSERTED.Id  INTO @MyTableVar
VALUES  (@Name, @Username, @Password);


INSERT INTO [WH].[dbo].[UserRole] (UserId, Role )
SELECT Id, @Role FROM @MyTableVar;