SqlException: 'Procedure or function usp_UserValidation has too many arguments specified.'

Question

CREATE PROCEDURE usp_UserValidation
    (@UserName varchar(50),
     @Password varchar(30))
AS
BEGIN
    DECLARE @UserID int, @RoleID int;

    SELECT @UserID = UserId, @RoleID = RoleId 
    FROM Users
    WHERE UserName = @UserName AND Password = @Password

    IF @UserID IS NOT NULL
    BEGIN
        IF EXISTS (SELECT UserId FROM Users WHERE UserId = @UserID AND IsActive = 1)
        BEGIN
            SELECT 
                @UserID [UserID],
                (SELECT RoleType FROM Role WHERE RoleId = @RoleID) [RoleType] -- User Valid
        END
        ELSE
        BEGIN
            SELECT -2 [@UserID], '' [RoleType] -- User not activated.
        END
    END
    ELSE
    BEGIN
        SELECT -1[@UserID] , '' [RoleType] -- User invalid.
    END
END

Dapper

public (int UserId,string Roletype) UserAuthentication(UsersViewModel users)
{
    DynamicParameters parameters = new DynamicParameters();
    parameters.Add("@UserName", users.UserName);
    parameters.Add("@Password", users.Password);
    parameters.Add("@UserID", dbType: DbType.Int32, direction: ParameterDirection.Output);

    connection();

    sqlConn.Open();           
    var res = SqlMapper.Query(sqlConn, "usp_UserValidation", parameters, commandType: CommandType.StoredProcedure).SingleOrDefault();
    int userid = parameters.Get<dynamic>("@UserID");
    string roletype = parameters.Get<dynamic>("@RoleType");            
    sqlConn.Close();            
    return (userid, roletype);
}

I'm trying to implement the authentication on given User credentials. The stored procedure works fine in SQL. Getting an error on the SqlMapper.Query line while running the application.

What am I missing here?

Answer 1

(var userid, var roletype) = sqlConn.QuerySingle<(int, string)>("usp_UserValidation", 
    new { users.UserName, users.Password }, commandType: CommandType.StoredProcedure);

the key change here is that the results are in the select, not via parameters; the other changes just simplify the code.