Hi,

I prefer to use static SQL in all cases where possible and only use dynamic SQL when there are a lot of where clause criteria to the stored proc. This is due to:

- Far easier writing and debugging

- Reuseable execution plans (in most cases)

- Much tighter security

Easier writing

With static SQL you have WYSIWYG with syntax highlighting and no problem with missing spaces, spelling mistakes etc.

Reusable execution plans

When resorting to dynamic SQL remember that the query plans are not reuseable when you use the EXEC(). Using the "sp_executesql" gives you that option along with the possibility of using both input and output variables. "sp_executesql" is almost always superior in both flexibility and performance to the EXEC() alternative.

Tighter security

With static SQL you can grant access to stored procs onlyt. With dynamic SQL you will have to grant the executing user SELECT access to all the references tables. That may work for you, but it's definitely not to my taste.

To sum it up there are certainly situations where dynamic SQL is beneficial, but it is necessary to understand the implications that may tag along.

Sincerely,

Lasse