There's one of those “holy wars” going on in the blogsphere. This time it is about the using Dynamic SQL vs. Stored procedures. But this holy war is an important one. It's one that I have learned a lot from, and I'll admit it shamelessly. I didn't know all those good things about Dynamic SQL. I really didn't I guess it's that kid of experience that you can learn form others who have done a great deal more than you in that area, and I am not a database guru by all means.
I did have the misconception that dynamic SQL is bad and that stored procedures are the cure(albeit painful at times) to the ills of SQL injection and program maintenance.
Rob howard started it (oh god that sounds awful) and got lots of very insightful comments (read them). Then Frans Bouma followed with a scathing and to the point response and these have been other responses in the blogsphere. Then Rob answerd back to Frans . I urge you to read the whole discussion, as it is enlightening.
I always thought of dynamic SQL as a plague because I only knew the “bad”way of doing it: concatenating strings into WHERE clauses. I admit that I was not aware of the possibility to use sp_executesql and actually creating parameters in the strings passed. That makes the whole situations different when I think of it. You should read the discussion mainly because if there's one thing to “get” from all this is that there is no silver bullet. There are pros and cons for each way of doing things, but you need to have both of these tools on your belt. I really regret not having exposed myself to this earlier.
A very nice article that explains the dynamic SQL approach is found here and here is the blog that points to it which contains some time comparisons of these approaches(via Rob howard).
Ah.. I fulfilled my mantra for today: “Finish the day with at least one thing you did not know before it started”