I have done it before. You have to write your entire statement to a variable and then use sp_executeSQL to process it.
With that said IF I knew then what I know now I would not do it. I would instead have used Table UDFs that have parameters passed into them and avoid the whole dynamic SQL portion.
Unfortunately, I have left that company, a friend of mine still calls the procedure during their month end and it works nicely, but, I'm still unhappy about it.
Good Hunting!
AJ Ahrens
webmaster@kritter.net