You've basically got three choices. (two of which Howard already pointed out).
Monte Sano (State Park) is up on Monte Sano Mountain on the east side of Huntsville. I'd say it's about a 20 minute drive from the Madison Quarry. They offer a nice campground that is secluded away from Huntsville and in a wooded setting. Both Tent and RV camping. They have a seperate "primative camping area" also that only includes water if you want that. They've got nice hiking trails along the top of the mountain also.
see
Alabama State Parks
Point Mallard (municipal park) is in Decatur on the Tennessee River. It's got a nice campground also with most of it in the shade. All spots are RV compatible, but some don't include sewer is the only difference. If you're driving up from this south, this will be a little closer than driving on to Monte Sano. Pt Mallard also has an Aquatic Center with wave pool and olympic diving pool, tube rides, etc... (They will close the aquatic center at the end of Sept I think). Also a golf course, rec centers, softball fields, indoor ice rink, walking trails on the river, etc. Pretty nice place.
Point Mallard Park - Family Recreational Fun Land, Decatur, Alabama
Space and Rocket Center - This is in Huntsville right on I-565 at Sparkman Drive. It's about 10 minutes from the Quarry. They have a small campground behind the space museum. I know some people who've stayed there and they were ok with it, but don't have any information about it. I know they have full RV hookups though. It's not well advertised and most folks don't even know it's there. Call the Space Center to inquire. I couldn't find anything on their website, but here it is for contact info.
Home - Space Camp® Online
There is some "primative camping" at a couple of the wildlife management or other government area, but they can be pretty trashed sometimes and can't say I'd reccomend them.
If you've got military access to base, there is a decent campground on Redstone Arsenal also for active or retired military personel.