The temp table's scope is connected to the session, not the variable. After he uses exec @STR, he can select out of #temp. A table variable would not...

Instead of using INSERT INTO #tmp SELECT... FROM....., use SELECT...INTO #tmp FROM....

Is there a reason you are using dynamic SQL instead of compiled stored procedure?

The IIF statement is the same as an IF statement. The first parameter is the conditional statement. The second parameter is the return value if the condition is met. The...