After seeing BananaBro's answer, I was going to make this a comment on the Singles portion but decided to make this an answer instead. While Acid Spray isn't used much competitive, Acid Spray has seen some usage in Singles. While yes, it is generally better to boost your own stats, there are a couple of reasons why Acid Spray is used in Singles.
1. It allows Pokemon who do NOT have a way to boost their own Special Attack to be able to break past defensive Pokemon.
Some offensive Pokemon do not have a way to boost their Special Attack stat (not counting Petaya Berry or Weakness Policy). However, by using Acid Spray, it can allow offensive Pokemon to break past Pokemon that could otherwise switch into them, and even 1v1 certain Pokemon. Some examples are Goodra in SV PU (and NU if you decide to use there, was used in Alpha SV NU), Hisuian Goodra in SV RU, Skuntank in ORAS NU and SM PU, and Eelektross in SM PU and SV ZU.
And yes, the opponent can just switch out to get rid of the Special Defense drop, which leads to the other reason:
2. Opposing Pokemon switching out is EXCACTLY what the Pokemon is using Acid Spray for
Opposing Pokemon switching out is another reason for some Pokemon using Acid Spray in competitive. The offensive Pokemon I listed earlier can also use Acid Spray to force switches. Defenisve Pokemon can also utilize Acid Spray to force switches. Tentacruel in ORAS UU (and I guess SV NU as well) can utilize Acid Spray to force Pokemon to switch out. Another example is Fezandipiti in National Dex Ubers(original VR nom post if you're interested, can also find other VR posts about Fezandipiti), as it can utilize the Special Defense drop from Acid Spray to force switches (and support breakers such as Deoxys-A), and can switch out with U-turn should the opponent switch out after you use Acid Spray.
I'd also like to mention that Pokemon using Acid Spray is also dependent on the generation and format. While the utility it has its nice, some Pokemon might prefer other options or use another set that doesn't need Acid Spray. For example, Tentacruel in SM UU prefers other utility options over Acid Spray. While Fezandipiti runs Acid Spray in NatDex Ubers, it doesn't use it in formats such as SV RU or OU, where it uses other sets that don't need Acid Spray. While Skuntank can use Acid Spray in ORAS NU and SM PU, it doesn't use it in Gen 8 since it can learn Nasty Plot. It also depends on what Pokemon is using Acid Spray, as you're not going to have Acid Spray on something like a Salazzle or Naganadel.
Edit: Acid Spray is viable in a format like 1v1, where you're not allowed to switch out, allowing offensive Pokemon to easily break past defensive Pokemon there.
TL;DR Whether or not Acid Spray is worth it in competitive is dependent on the Pokemon, generation, and format. Offensive Pokemon with no viable way of boosting their Special Attack stat can use Acid Spray to allow them to break past checks, force switches, or both. Defensive Pokemon can also utilize Acid Spray to force opposing Pokemon to switch out. While some Pokemon benefit from what Acid Spray does, they don't want to use it in certain formats since they prefer other options or is used in a format where it uses other sets that doesn't benefit from using Acid Spray. Acid Spray is also a viable move in 1v1 formats since you're not allowed to switch out.