What is the procedure to create my own commercial?

Why should I create dynamic SQL in a stored procedure?

• I am faced with the need to do dynamic SQL on a complex search query. I haven't done dynamic SQL since the days of ASP(using .Net now), and I am reading a lot about how I should do this in SQL in a stored procedure. But I'm curious why? Is there a performance increase? Why can't I just pass in one I create in codebehind? I use Stored Procedures for all of my static queries, or ones I pass parameters into, but it seems like this is a lot of SQL coding for the same thing that could be accomplished in code behind. Any help would be appreciated:)

• Answer:

  In short Security & Performance. Security: I like to have my SQL servers setup such that no ad hoc SQL can be run by the web user, it can only execute stored procedures. This goes a long way in preventing SQL injection attacks. Obviously this won't be an option if you create ad hoc SQL within your code. This means you have to constantly play catchup with the injection kiddies out there as they come up with more "creative" ways to hack your SQL server via passed parameters. This is especially true when you are using user generated parameters in ad hoc SQL. Performance: You probably know that there is a tremendous performance difference between ad hoc sql and stored procedures. SQL is able to cache & optimize the stored procedures because it knows what's coming, with ad hoc sql it can not do this. Obviously the performance takes a huge hit when you start doing dynamic sql within a sproc, but I'm willing to bet that there is still an advantage to doing it in the sproc. Avoid it: I'm not a SQL expert, I focus more on the application side of things, but the SQL experts that I look up to avoid dynamic SQL at all costs. It's not as easy, clean, intuitive or maintainable as dynamic SQL, but when it comes to performance having 8 if statments with slightly different SQL statements is much faster.