The procedure itself is fine, it would only be vulnerable if you were doing some kind of sql generation and execution inside it. You are however correct that you are doing the attack incorrectly, but it's not a problem with your procedure.
SQL Injection attacks happen when you execute sql you generate using string manipulation instead of parameters. You don't mention what language you are executing the stored procedure from, but most have libraries that will allow you to specify that you want to execute a stored procedure and pass parameters as values instead of passing a string that does
exec myproc 'value1', 'value2'.
If you are doing the latter and generating the entire sql query yourself such as
string input1 = "value1", input22 = "value2"; string sql = "exec myproc '" + input1 + "', '" + input2 + "'", then that is where injection occurs. This example would have sql of
exec myproc 'value1', 'value2'. This code is vulnerable to sql injection if
input2 is provided by a user or another system. Someone could enter this for
'; drop table mytable; --
sql string you inject then becomes:
exec myproc 'value1', ''; drop table mytable --'
The apostrophe at the start of the input string closes the apostrophe added by you, the semicolon indicates a new statement, you inject the drop table sql, and the hyphens at the end tell it the rest of the line is a comment so the closing apostrophe added by the code does not cause a syntax error
Your test simply passes a string as the
@password parameter to your stored procedure, the fact that the contents look like sql to a human doesn't matter.
EXEC test @password = '0; drop table credentials;'