dynamic table names?

I saw a question on lazydba today that I wanted to post about. A person asked if it was possible to pass in a table name as a "bind" variable to a procedure.

If you're doing that, I assume you're trying to use it in a FROM or JOIN statement.

To accomplish it, you would have to use dynamic sql. Passing a table name in a variable won't work otherwise.

Why dynamic sql is evil!

1) You must grant privileges to underlying objects, execute on the procedure will not be sufficient

2) The query plan SQL stores will not be appropriate for every execution of the procedure. So you either have a crap plan or SQL has to recompile.

3) They are a giant PITA to troubleshoot. Inevitably, you end up pulling the dynamic statements out of the proc and running them manually to find the problem.

Avoid dynamic SQL like you would a rabid animal

--matt