SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Dynamic SQL or Stored Procedure

By Andy Warren, (first published: 2003/04/23)

Dynamic sql is much talked about here on the site (click here to view a search on it), both in articles and in the discussion area. Robert Marda has a great series up that talks about it. What I'd like to do here is provide something that you can give to the new developer who has to make a design choice.

What is dynamic sql? It's any sql query built up on the client and submitted to the server, or it can be a query built up and executed inside a stored procedure. A good example is to think of building a search form where you can enter a half dozen different elements, you build the query at run time based on which fields in the form were populated.

What makes a query dynamic? If you're supplying the table name at run time, customizing the select list or the where clause, it's dynamic. If you're using ADO and doing a connection.execute and passing a built up string, it's dynamic. If you're building up a string variable inside a stored proc and running it using either exec() or sp_executesql, it's dynamic. If you're just building all your queries in your app so you don't have to go through your DBA, they are all dynamic.

Why is it bad? Three reasons. First, you generally don't get the benefits of a compiled query plan. I say generally because if you do it carefully, you can get some plan reuse. The idea is it takes SQL some amount of time (that varies based on the complexity) to figure out the best way to get the data. It saves that plan so that when you re-run the query, you don't have to figure out the plan all over again - saving you time and server expense. Second, it provides a huge opportunity for a sql injection attack. Coding to protect against injection attacks is more work, and easily forgotten. The final reason is security. Dynamic sql requires that security be evaluated for every object referenced, which means you usually have to grant access to the base tables, something to be avoided if at all possible.

What is a static query? Pretty much just the opposite of dynamic. It's a stored procedure, not code sent from the client app. SQL will compile and preserve the query plan, making it faster to execute. If the owner of the procedure is the owner of all the tables being used in the query, no further security checks are done, making it faster (ownership chaining). No chance of injection attacks.

How can I avoid it? As Robert points out in his series, you can do some coding in a stored procedure that essentially provides a separate block of code for each set of possibilities. You can also use like instead of equals in the where clause. Sometimes this is a good idea, sometimes not. It means more code to maintain and debug. I recommend using this when the number of possibilities is small or you really, really need that extra bit of performance, or you truly cannot grant access to the base tables.

How can I do it safely? A good rule to follow is never to grant access to the base tables. My recommendation is to build a view for each table (and consider if it really needs to all columns) and grant only select access on the view. Use only the view when coding dynamic sql. You still take the performance hit of the security check, but there is no possibility that the user can do an injection attack containing a delete * from table. Hopefully you're running with minimal permissions so that there is no possibility of the user dropping objects or adding users to the server.

How much of a performance hit does it cause? Good question and one that is hard to answer. Not a lot. It's probably only going to matter if you're running the query a lot - lots of users submitting variations of the query continuously. The performance hit is a combination of the time required to resolve the security issue and the time to generate a query plan. Of the two, both happen pretty fast but I suspect the query plan takes longer.

How can I minimize the performance hit? SQL gives you the option of using exec() or sp_executesql. Of the two, sp_executesql is the better choice, it will try to re-use previous plans that match the query exactly. You can boost this some by using parameters in your sql, something I rarely see done. SP_executesql also has the advantage of supporting output parameters so you can return info from the dynamic session back into the calling stored procedure. ADO automatically uses sp_executesql when you do a connection.execute.

Is it better to build the query on the client or in a stored procedure? You use a stored procedure for all the reasons outlined in "Why is it bad?" above, plus one more - loose coupling. Putting data access code in a stored procedure allows you to change the behavior without recompiling the application. So which is better depends on whether that loose coupling is of any benefit to you. Another point to consider is how changes get applied. Once you code a proc, all changes go to the DBA (right?!) who reviews and applies them to the production box. If you put the code in the application, you can change it at any time. I'm not normally in favor of circumventing the DBA, but this may be a valid case. In my opinion once you decide to use dynamic sql, I think it should be built on the client. The string handling is usually better and you can usually build/debug/test faster and easier in application code.

Are there categories of tasks that usually require dynamic sql? Sure. Searches as I mentioned earlier. Crosstabs where you don't know all the column names at design time. Decision support type queries (really a type of search). Pure ad-hoc queries like you would run in Query Analyzer (or Access, or another other tool that supports querying a sql database).

Is it really bad? It depends on a lot of things. If you're using zero stored procedures - which means 100% of your queries are dynamic - you're working the server harder than it needs to. If the server is running at 10% of capacity, no, it doesn't matter. If the client response time is fine, it doesn't matter. If you've coded defensively against sql injection attacks, then no, it's not really bad. A server represents a fixed amount of resources. The job of the DBA is to use those resources efficiently and to secure access to the data. If you need to run dynamic sql to provide your users with a search function, the DBA is not there to say no, you can never do dynamic. But the DBA should be reviewing it to make sure it no injection attack is possible, and to determine if there is a way to convert it to a static query.

Realistically you can't avoid dynamic sql. You can employ some tricks, but often at the expense of time to build and maintainability later on. Still, the tricks are worth knowing. Hopefully I've given you something to think about, and maybe answered some questions you hadn't thought to ask yet. Got more questions? Post in the attached discussion area and I'll do my best to answer them.

Total article views: 47292 | Views in the last 30 days: 16
Related Articles

Difference between Normal Stored Procedures and Dynamic stored procedures

Normal Stored Procedures and Dynamic stored procedures


SQL stored Procedures

building dynamic queries


Stored procedure slower then query

Stored procedure slower then query


Stored Procedures flexibility

How can a developer use a stored procedure instead of programmaticaly building the query with code?


Using the MERGE Statement in SSIS Via a Stored Procedure

Step-by-step instructions how to build a stored procedure that dynamically constructs and executes a...

performance tuning    
sql puzzles    
stored procedures