February 11, 2002 at 5:43 am
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
February 11, 2002 at 6:52 am
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
February 11, 2002 at 6:56 am
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
February 11, 2002 at 6:59 am
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.
February 11, 2002 at 7:04 am
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
February 11, 2002 at 7:23 am
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.
February 11, 2002 at 8:29 am
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
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 12, 2002 at 12:00 am
Comments posted to this topic are about the item When to Use Dynamic SQL
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 12, 2002 at 12:31 pm
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
February 16, 2002 at 6:05 pm
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
February 17, 2002 at 6:55 am
I removed the duplicate postings you mentioned.
Why would your procs change so often? And in what way do they change?
Andy
February 19, 2002 at 4:06 am
Thanks for removing the duplicate posts!
Our stored procedures change so often because we are always adding new features. For example we will soon be modifying a feature so that instead of only being able to select one state or one country per search you will be able to select multiple states and/or multiple countries. This change will effect about 40 to 60 stored procedures.
We are competing in a niche market and must continue to add value for our customers or risk loosing them to someone else who is adding more value than we are.
Robert Marda
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
February 19, 2002 at 4:56 am
I hear you!
My only thought on this is that most companies tend to fall into the same trap, adding functionality over time. It makes sense to trickle out changes rather than do "big" upgrades. What we try to do is identify places where we expect to add functionality and try to plan so that we don't have to make a lot of changes later to fit that added piece in. Not simple and not always successful, but every time you win it helps.
Im curious though as to why it would affect 40+ procs? Seems like some very tight coupling?
Andy
February 19, 2002 at 5:52 am
I use dynamic sql for converting hex
set@sql = 'set @bin = 0x' + @char
set@parm = '@bin varbinary(' + convert(varchar(10),len(@char)/2) + ') output'
exec sp_executesql @sql, @parm, @bin output
And for getting the output buffer.
Also writing a scheduler to give more flexible dependencies - you need to use dynamic sql to call stored procedures which are held in the dependency table (sp_executesql to get the output parameters back).
I have used it on systems which have searches on a lot of different parameters and different numbers of parameters as you can build up the query by adding new features rather than having to do a lot of checking of values - as these systems temd to have ever changing requirements.
Cursors never.
DTS - only when needed and never to control.
February 19, 2002 at 7:06 am
Seems like a function would work for that?
Andy
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply