SQL Server MSSQL procedure to insert data in a table

6qftjkof  于 2023-03-28  发布在  其他
关注(0)|答案(1)|浏览(153)

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

6ojccjat

6ojccjat1#

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
IDBOOK_NAMEBOOK_AUTHOR_IDQUANTITYBOOK_GENRE_ID
1Jopa123

fiddle

相关问题