This is what i've got and it still isn't working:

CREATE Procedure sp_mdLoginUser
@Username char(50)
As
DECLARE @UserID int
If (select count(*) from Users Where Username = @Username) >= 1
begin
SELECT @UserID = @@Identity
return @UserID
end
else
return(0)