table permissions, dynamic sql
An important point to remember when writing dynamic SQL is to ensure that the tables that you are using in your SQL statement should have 'select' permissions enabled for the query to work. if you can't allow select permissions on the table then avoid using dynamic SQL (this may be the case in most of the production environments).
I had the unfortunate experience of using dynamic SQL in a project without realizing the above issue. Everything worked in my system and in the test environment as well, but when the solution was deployed to production, the query did not work.
I found that, the users in the production environment had permissions to execute stored procedures alone, and for the dynamic sql to work ( which used EXEC ' ) required select permissions on the table. hence the stored procedure failed.
I re-wrote the query using COALESCE statement to workout the where clause. The link below touches on how to build dynamic where clause.
http://www.sqlteam.com/i