Chances are you can accomplish your objectives using standard SQL, with maybe a little help from temporary tables, table variables, or case statements. I don't understand what you are trying to do, but your code looks like SQL written by a VB programmer (loops, inserting parameters, etc...). These are legitimate tools for SQL, but most procedures don't require them.
Using a dynamic SQL statement may be an option, either to assign the value to @str or for the insert statement, but dynamic SQL is tricky because it executes in a distinct environment and your variables go out of scope.
If you could briefly describe your application, relevant table structures, and the task you are trying to perform, I may be able to give your some programming algorythms that are more appropriate for SQL.