Why is my SQL code not executing the stored procedure

Question

So the objective of this is to show security so this is being done under a guest user account. I gave access to the guest user to execute this code. However I keep getting a NUll error

Cannot insert the value NULL into column , table ; column does not allow nulls. INSERT fails. The statement has been terminated. The 'vc_AddUserLogin' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

So I have already tried to EXEC vc_AddUserLogin 'The Doctor', 'Gallifrey'

Here is the code for the procedure

CREATE PROCEDURE vc_AddUserLogin(@userName varchar(20) , @loginFrom varchar(50)) 
AS
BEGIN
    DECLARE @userID int
    SELECT @userID = vc_UserID FROM vc_User
    WHERE UserName = @userName
    INSERT INTO vc_UserLogin (vc_UserID, LoginLocation)
    VALUES (@userID, @loginFrom)
    RETURN @@identity 
END

The expected result should be a updated userid and login. I am not looking for a answer just someone to point me in the right direction because I am lost. This should be easy and I am probably overthinking it.

Answer 1

Do not return values from a stored procedure, other than as status for the procedure. Instead, use an OUTPUT argument.

I also recommend using an OUTPUT clause, as in:

CREATE PROCEDURE vc_AddUserLogin (
    @userName varchar(20) ,
    @loginFrom varchar(50),
    @id int OUTPUT
) AS
BEGIN
    DECLARE @ids TABLE (id int);  
    INSERT INTO vc_UserLogin (vc_UserID, LoginLocation)
        OUTPUT id INTO @ids  -- "id" is the name of the id column in vc_UserLogin
        SELECT u.vc_UserId, @loginFrom
        FROM vc_users u
        WHERE u.UserName = @userName;

    SELECT @id = id
    FROM @ids;
END;

You then call this code as:

DECLARE @id int;

EXEC vc_AddUserLogin @userName, @loginFrom, @id OUTPUT;

SELECT @id;

This will return NULL if no users match @userName.