There are four different vehicles that you could be considering:
Examples will be focused on the Western Front, I don't know about too many Soviet or Japanese vehicles.
Self-propelled howitzers (primarily intended for indirect fire, and often simply referred to as self-propelled artillery), notable ones being the Hummel, Wespe, Priest, and Sexton. There were many others of varying gun sizes, they tended to be relatively lightly armoured (using older tank chasis). Though even vehicles such as the katyusha would be in this category.
Assault guns on the other hand were primarily used in the direct fire role. These are likely what you are thinking of. The famous Sturmtiger and others such as the bruumbar were in this category. Armed with forward mounted, typically large caliber and short barreled gun with limited traverse. They were intended to provide close infantry support, especially in urban areas and to breach defensive positions.
The Western Allies typically used regular tanks in this role, though specialized variants were not uncommon, such as the Sherman 105, or the AVRE could fill this role).
Tanks destroyers also fall into two categories:
American doctrine tank destroyers were just lightly armoured tanks with powerful guns (i.e. M10 Wolverine, M18 Hellcat).
And, like you are probably referring to the German and Soviet [and one British] ones. Forward mounted gun on an armoured chassis, initially they were lightly armoured, later they were some of the most heavily armoured vehicles. Very similar to assault guns, they typically used longer guns, better for armour penetration than the large high-explosive rounds of the assault guns. The Marder, Hetzer, jagdpanther, and elephant epitomize this vehicle.
Now you may have noticed that I missed two of the most famour vehicles, the German Sturmgeschutz III and the Soviet ISU-152.
The StuG III was designed as an assault gun, but it proved to be an effective tank destroyer and was later (as of Ausf. F) outfitted with an longer gun, more effective against tanks. The StuG IV was primarily an anti-tank weapon.
The ISU-152 was designed and primarily used as a self-propelled howitzer, but also proved effective as an assault gun and tank destroyer (its shell was so powerful it didn't need to penetrate armour to get kills).
These show that yes they were versatile and in some cases a self-propelled gun could fill several roles.
Self-propelled artillery can refer to all of the above, or different segments. Typical use of the term, in my experience refers either to either to just howitzers or all of the above that are lacking turrets (except possibly if the howitzer is turret mounted).