MSSQL procedure to insert data in a table

Question

I'm trying to create a procdeure to insert data in a table, but there is an error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

The procedure:

CREATE PROCEDURE InsertBOOKS
    -- Add the parameters for the stored procedure here
    @BookName varchar(30),
    @Author_ID INT,
    @Quantity INT,
    @Genre_ID INT,
    @BookID int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    INSERT INTO BOOKS
    (BOOK_NAME, BOOK_AUTHOR_ID, QUANTITY, BOOK_GENRE_ID)
    VALUES (@BookName, @Author_ID, @Quantity, @Genre_ID);

SET @BookID = @@IDENTITY
END



DECLARE @return_value int,
        @BookID int

EXEC    @return_value = [dbo].[InsertBOOKS]
        @BookName = N'Jopa',
        @Author_ID = 1,
        @Quantity = 2,
        @Genre_ID = 3,
        @BookID = @BookID OUTPUT

SELECT  @BookID as N'@BookID'

SELECT  'Return Value' = @return_value

0 ideas how to fix it

Answer 1

i am not sure what you expect from return_value, but to get the last inserted id, you can use SCOPE_IDENTITY()

CREATE tABLe BOOKS
    (ID INT IDENTITY(1,1),BOOK_NAME varchar(100), BOOK_AUTHOR_ID int, QUANTITY int, BOOK_GENRE_ID int)

CREATE PROCEDURE InsertBOOKS
    -- Add the parameters for the stored procedure here
    @BookName varchar(30),
    @Author_ID INT,
    @Quantity INT,
    @Genre_ID INT,
    @BookID int OUTPUT
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    -- Insert statements for procedure here
    INSERT INTO BOOKS
    (BOOK_NAME, BOOK_AUTHOR_ID, QUANTITY, BOOK_GENRE_ID)
    VALUES (@BookName, @Author_ID, @Quantity, @Genre_ID);

SET @BookID = SCOPE_IDENTITY()
END

DECLARE @return_value int,
        @BookID int

EXEC    @return_value = [dbo].[InsertBOOKS]
        @BookName = N'Jopa',
        @Author_ID = 1,
        @Quantity = 2,
        @Genre_ID = 3,
        @BookID = @BookID OUTPUT

SELECT  @BookID as N'@BookID'

SELECT  'Return Value' = @return_value

@BookID
1

Return Value
0

SELECT * FROM BOOKS

ID	BOOK_NAME	BOOK_AUTHOR_ID	QUANTITY	BOOK_GENRE_ID
1	Jopa	1	2	3

fiddle