tsql - SQL: Set variable to a cell value inside a stored procedure

Question

I am trying to store a single cell value from one table in a variable (inside a stored procedure), so I can use it to edit a value in another table, but I keep getting a MSG 201:

Procedure or function 'spBookReturn' expects parameter '@bookID', which was not supplied.

Every time I try to run the sp where it all should happen:

CREATE PROC spBookReturn
@loanID UNIQUEIDENTIFIER,
@bookID UNIQUEIDENTIFIER OUTPUT

    AS
    BEGIN
    BEGIN TRANSACTION tBookReturn
        UPDATE BorrowedMaterial SET returned = 1, returnedDate = GETDATE();
        SET @bookID = (SELECT TOP 1 bookID FROM BorrowedMaterial WHERE loanID = @loanID ORBER BY returnedDate);
        UPDATE Books SET nHome = nHome + 1 WHERE ID = @bookID;
    COMMIT TRANSACTION tBookReturn;
    END;

EXEC spBookReturn '546A444A-3D8D-412E-876D-2053D575B54F'

Does anyone know why the way I have defined the @bookID variable doesn't work and how I can make it work?

Thanks.

EDIT: I got two tables: BorrowedMaterial that includes the attributes loanID, bookID, returned, returnedDate and a few others that's not relevant. The other table is Books and it includes bookID, nHome but not the loanID.

So by giving only the loanID as an input, I would like to update the nHome. I am trying to grab bookID since it is the only thing the two attributes got in common and this is where the issues happen.

Side note: I removed the variable @custID it spawned by mistake.

question from:https://stackoverflow.com/questions/65920093/sql-set-variable-to-a-cell-value-inside-a-stored-procedure

Answer 1

All parameters for a procedure are Input parameters. If you declare a parameter as an OUTPUT parameter, it is still an input one, and if it doesn't have a default value must be supplied.

If you want the OUTPUT parameters to be option, which I personally find can be quite often, then give them a default value. I also add some additional logic to your procedure, as you should be using an TRY...CATCH and an ORDER BY in your query with a TOP.

CREATE PROC dbo.spBookReturn @loanID uniqueidentifier,
                             @bookID uniqueidentifier = NULL OUTPUT,
                             @custID uniqueidentifier = NULL OUTPUT
AS
BEGIN
    BEGIN TRY --If you are using tranasctions, make sure you have a ROLLBACK and THROW for errors
        BEGIN TRANSACTION tBookReturn
        UPDATE BorrowedMaterial
        SET returned = 1,
            returnedDate = GETDATE()
        WHERE loanID = @loanID;
        /*
        UPDATE BorrowedMaterial
        SET returnedDate = GETDATE()
        WHERE loanID = @loanID;
        */
        SET @bookID = (SELECT TOP 1 bookID
                       FROM BorrowedMaterial
                       WHERE loanID = @loanID
                       ORDER BY ???); --A TOP should have an ORDER BY

        UPDATE Books
        SET nHome = nHome + 1
        WHERE ID = @bookID; 

        COMMIT TRANSACTION tBookReturn;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION tBookReturn;
        THROW;
    END CATCH;

END;

Then you can execute the procedure as you have, without @bookID and @custID being passed. Of course, if you don't, their values will be "lost" in the calling statement. If you need them, then pass their values too in the EXEC:

DECLARE @bookID uniqueidentifier, @CustID uniqueidentifier;

EXEC dbo.spBookReturn @loanID, @bookID OUTPUT, @CustID OUTPUT;
--SELECT @bookID, @CustID;