Hi Sir Gail..
Please see my Attachment..that is the result when i try to run your suggestion...
Look at what the error say and see what I almost did to your server by injecting a command into your dynamic SQL and now ask yourself what it that had been a DROP DATABASE instead of a shutdown which couldn't run?
What would your boss say when you put that into production and someone a little less ethical deletes data, steals your paswords, drops your database, all because you decided that unparameterised dynamic SQL was easier...
Sean, What do you mean i don't have a parameter??
Your dynamic SQL is not parameterised, hence why I could do nasty things just by adding extra commands (that get executed) to the stored procedure parameter value.
If you aren't willing to learn how to write dynamic SQL safely, then please stop writing it at all, for your company's sake and the sake of all their customers. Oh, and stop running your queries as SA too.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass