How much weight can a 2 inch screw in a wood stud hold?
How much load a screw can hold does not depend on its length, assuming it is long enough.  Instead, the load is a function of its cross-sectional area.  A typical range for proof strength for steel is 50 to 100 kpsi (i.e., a screw with a 1 square inch cross-sectional area of steel can hold up to 50,000 to 100,000 pounds).

See here and here.

Of course, as the diameter of your screw increases, the stud is likely to fail first.