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


When to Use Dynamic SQL


When to Use Dynamic SQL

Author
Message
Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 113
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/whendynamicsqlisuseful.asp

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Andy Warren
Andy Warren
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: Moderators
Points: 25093 Visits: 2746
Hey Robert,

Good to see another article from you! Thanks for submitting. As far as dynamic sql, I've never worried about the performance (solve the problem, then seek performance if its an issue) rather the weakness in the security model that forces you to grant table access to the user of exec. I know Steve likes to use a solution where is has one proc per possible query but I think at some point that doesn't scale. A good example would be a search form with 9 or 10 fields - thats a lot of possibilities! Any ideas?








Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 113
mbova:

I think the major thing about security is this:

Normally you can give execute permissions to a user for a stored procedure and the stored procedure will run fine.

However, with dynamic SQL in a stored procedure the permissions do not carry over to the code that is dynamic and so if the user has execute permissions to the stored procedure but not to a table in the dynamic SQL then they will get a permissions failure error.

In our database all users by default have read permissions so for us this is not an issue.

Robert Marda

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 113
Sorry for so many posts that are the same. I kept getting an error stating there was a problem and thought the post had not gone through.

Robert Marda

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Antares686
Antares686
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26456 Visits: 785
I have used both static and dynamic queries in Stored Procedures for a long time now. I tend to try to avoid dynamic since I found out tht was the reason why I had to grant read access to tables so often. But I do agree with your statment:
quote:
I use dynamic SQL whenever it seems to be the best way to get the results I need and I firmly believe there is a place for dynamic SQL in almost every database
Good article on the subject.



Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 113
Andy:

We have some stored procedures that have 25 to 27 different fields a user can modify. These directly affect how the stored procedure limit the result set. About 6 months ago we converted one of our dynamic SQL stored procedures (the one that handles 27 different fields) from dynamic SQL to static. Now, instead of having one stored procedure we have 23 stored procedures. Many of them still have some dynamic SQL in them otherwise they would have been slower than the original. For the most part we have gained in performance and speed at a cost of extra maintenance.

Robert Marda

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Antares686
Antares686
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: Moderators
Points: 26456 Visits: 785
In regards to what Robert said.
quote:
In our database all users by default have read permissions so for us this is not an issue.
I prefer to deal with this by creating roles to handle. First I hae a role called urProcExec which is the role for people with execute rights on the procedures and then I create a role called urReadTbl and put urProcExec in it giving rights on all tables to be read. This makes it easier for me to keep up with permissions and I can prevent user access better on tables that need not have any permissions in regards to procedures with dynamic SQL.



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (24K reputation)

Group: Moderators
Points: 24464 Visits: 1917
mbova, here's the deal on security.

If we build stored procedures that contain only static SQL statements, we can take advantage of ownership chains. For instance, dbo owns the stored procedure and dbo also owns the tables and views referenced by the stored procedure. Because all the objects have the same owner, SQL Server will only check security when the stored procedure is executed. It makes the assumption that since the owner of the stored procedure also owns the objects referenced, the owner intended the person with execute rights on the stored procedure to have the appropriate rights to carry out the SQL statements within the context of the stored procedure. So if I don't normally have DELETE rights on a table, but a stored procedure I have the right to execute does a DELETE on the table, SQL Server will allow the DELETE through the stored procedure. If however, I were to try and execute a DELETE separate from the stored procedure, SQL Server will balk and prevent me from doing so. As a result, we can strictly control how data is accessed and modified. We simply give execute rights on the stored procedures and do not grant any rights on the tables and views.

When dynamic SQL enters the picture, however, things change. Even if I choose to execute a dynamic SQL statement from within a stored procedure, SQL Server will execute that dynamic SQL statement in a new context. It will be executed outside the context of the stored procedure, basically as an ad hoc query. SQL Server will check security with respect to the dynamic SQL statement, something we avoided in the static SQL inside a stored procedure example. Ownership chains become irrelevant with respect to the dynamic SQL statement.

That means if the DELETE is contained in a dynamic SQL statement and I don't have rights to DELETE from the table, SQL Server will balk. So what has to happen is where previously a user did not have to be granted DELETE permissions on the table, he or she now does. That means that the user can access and modify the data outside a stored procedure, in any manner rights allow.

K. Brian Kelley
bkelley@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@‌kbriankelley
Steve Jones
Steve Jones
SSC Guru
SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)SSC Guru (144K reputation)

Group: Administrators
Points: 144368 Visits: 19424
Nice article with good examples. Personally, though, I have a rule of thumb: NEVER use dynamic SQL. That being said, I have used it in places, but only when I have tried every thing I can think of. Same as with cursors, you may need to use dynamic sql, but probably not. I think it should be your last resort, meaning you HAVE tried other solutions.

I also implement multiple stored procedures because the maintenance cost is less than the performance cost.

Regarding the article, I agree that the opening could be beefed up to present some refereces or reasons not to use dynamic sql. Lots of newbies will skip the short intro and start cutting and pasting code. Otherwise it was informative.

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Robert W Marda
Robert W Marda
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2885 Visits: 113
I guess part of the reason I am not against dynamic SQL is that when I arrived at bigdough.com in May 2000 almost all stored procedures were dynamic SQL. I have seen dynamic SQL in action a lot and it works very well for us. I am seeing that there are benefits to using static SQL, however it takes many days just to convert one stored procedure from dynamic SQL to static and then more time to test it.

I am gradually coming to the conclusion that static is generally better as long as you have the personel to make mass modifications when new functionality must be added. All our major stored procedures are modified about every three months. Since there are only two of us that modify stored procedures it is still easier to make changes in 200 hundred or so stored procedures instead of 5 or 600 hundred stored procedures.

Robert Marda

Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search