I generally find Hungarian notation silly, however, there's a compelling argument to have them in functions and procedures — perhaps more than for views. The type of the variable matters just as much as where/how it was declared, such as in/out params.
That's the 'steelman' argument for them, as I understand it.
On the flip side... perhaps using techniques from the 80s with a programming language and paradigm also from the 80s... and that hasn't changed all that much... isn't all that crazy.
At the end of the day: the evaluation of a given technique such as I described, should be measured against if it makes the development experience simpler and more comprehensible. Being able to instantly tell if a given reference in a procedural query comes from a table a view, a variable, or a parameter, I find helpful since we're typically using many names from these different origins and contexts and frequently side-by-side, such as in a complex query driving the procedural code.
Of course, If you don't like that, or simply don't find it sufficiently "modern" or "fashionable" (my God, what might others think!?)... again, I invite you to do you.