Executing a string versus a static SQL Statement.
-
Which do you prefer to use in stored procedures? Building a string dynamically and then executing it or creating static SQL code? Do you ever mix these two techniques in the same stored procedures?
For me, I prefer building a string dynamically and then executing it. Too me this allows much more flexibility and is easier to maintain than static code and possibly 8 to 20 stored procedures to do the same thing done in one stored procedure that builds a string and executes it.
I have developed some stored procedures that have large blocks of both techniques in the same SP.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
From a security standpoint, I prefer static code. If I maintain ownership chains (for instance stored procs, views, and tables are all owned by dbo), I don't have to give end users any rights to the tables. I can have them do everything through stored procs. I control their access. So long as the ownership chain isn't broken, SQL Server assumes that since they have rights to execute the stored procedure, it'll allow them to do the operations specified in the stored procedure.
With dynamic queries, this isn't the case. When EXEC[UTE] or sp_executesql is used, rights are determined at the time of execution based upon the user executing the stored procedure. It is not determined based on the owner of the stored procedure. As a result, I have to open up the data to the operations necessary. What that means is that a user could come in with another client and perform operations against the data in ways that I may not have intended.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
I don't think we've had a problem with security since our web site uses one user login for all stored procedures. And data updates by our researchers are done on a seperate server and they all have read/write permissions using the default database roles.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
I prefer hard coded sql. That way I can optimize each query. Also, dyamic sql tends to remove some of the benefits of precompiled query plans.
Steve Jones
-
If Im going to use dynamic sql it will be on the client. The string handling tools and development environment are better (to me anyway!). I agree that hard coded sql in procs is optimal - once you find that you can't do that (even using Steve's trick of a case where you have different versions for each different scenario and you have 10 million of them).
Trading one pain for another of course.
Andy
-
So do you all avoid dynamic SQL as much as possible then? Do you have any stored procedures that use dynamic SQL?
Since I have been in my current position we have been converting some of our stored procedures from dynamic SQL to static. Usually we get a boost in performance and optimization, however most times our one stored procedure needs to be split into multiple stored procedures to properly tune for every possibility and this increases our maintenance load, especially when major changes are needed in all SPs.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
I try to avoid dynamic SQL whenever possible. Security is one issue, as I've already discussed (we have multi-user apps which have different levels of access) and the performance issues that Steve has hit on is the other. Also, static is easier to troubleshoot.
However, there are times that only Dynamic SQL can provide a solution, in which case I'll use it. But given a choice, I stick with static.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Be careful in that someone could exploit dynamic sql as a security breech.
I just implement multiple stored procedures and absorb the workload.
Steve Jones
-
I think the only cases I've implemented dynamic SQL is for sysadmin stored procs and the like. I too am paranoid when it comes to security. Just because you are paranoid doesn't mean someone is not out to get you!
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley -
Perhaps I am not paranoid enough. In my previous position as a dba I never created a single stored procedure and knew nothing about creating dynamic SQL. I learned this after becoming an SQL Programmer. Here the emphasis was on dynamic SQL and our database reflected that.
In our stored procedures the building of the query is controlled by flags. No one can just enter whatever code they want and expect it to get executed. In addition the accounts that can access these stored procedures don't have sa priveleges except for those used by those in our database department. Isn't that secure enough?
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems -
From a security perspective, you are probably okay. However, as I discussed earlier, if you want data manipulation to occur in certain defined ways that are strictly controlled (necessary in most of my environments), dynamic SQL is out because we want to force users through the stored procedures (even if there is only one user like for a web app) and not allow them to hit the tables directly.
Also, the loss of the execution plan can be an issue. Every time a stored procedure has to be recompiled, there are periods where an exclusive lock is needed, which can make the stored procedures serializable.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply