SQLServerCentral / Discuss Content Posted by Robert Marda / Article Discussions / Article Discussions by Author / When to Use Dynamic SQL / Latest Posts

RE: When to Use Dynamic SQL

Simon Sabin — Wed, 23 Oct 2002 05:27:00 GMT

There is a common belief, as you mention in your article, that dynamic sql does not have compile plans. This is not true.Any adhoc query i.e not an SP, is parameterised, compiled and put in the cache. Depending on the complexity of the query (sub tree cost) the query will stay in the cache until it is booted out because of another query. the main issue is with parameterisation, it doesn't really work. It is supposed to convert values into parameters so that when you call the same query with another value you can get the same plan.i.e select * from mytable where col1 = 1and select * from mytable where col1 = 2should result in the same parameterised queryselect * from mytable where col1 = @p1Because this doesn't work you should use sp_executesql and do the parameterisation yourself i.e the above becomessp_executesql N'select * from mytable where col1 = @p1', N'@p1 int', 1So if you use sp_executesql the performance will be the same as an SP, (except adhoc queries are booted out the cache before SPs).Articles to read on this arehttp://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/default.aspThis is detailed in depth somewhere but I can't find it at the moment.Simon SabinCo-author of SQL Server 2000 XML Distilledhttp://www.amazon.co.uk/exec/obidos/ASIN/1904347088

RE: When to Use Dynamic SQL

Robert W Marda — Tue, 22 Oct 2002 12:46:00 GMT

Forgive the delayed response.I think you were looking at my article called Dynamic SQL vs Static SQL part 2. That one has code in the 2nd case that uses the case function in the order by. In the article I wrote called How Dynamic SQL Can Be Static SQL example 1 shows how you can use the case function in the where clause.Robert W. MardaSQL Programmerbigdough.comThe world’s leading capital markets contact database and software platform.

RE: When to Use Dynamic SQL

Oblio Leitch — Fri, 13 Sep 2002 12:02:00 GMT

The static SQL version of the second case.

RE: When to Use Dynamic SQL

Robert W Marda — Fri, 13 Sep 2002 11:25:00 GMT

Which example are you looking?Robert MardaSQL Server will deliver its data any way you want it when you give your SQL Programmer enough developing time.

RE: When to Use Dynamic SQL

Oblio Leitch — Fri, 13 Sep 2002 11:07:00 GMT

I would love to do with the WHERE clause what you do with the ORDER BY. Is there a way?

RE: When to Use Dynamic SQL

Steve Jones - SSC Editor — Mon, 04 Mar 2002 10:12:00 GMT

No issues here. IE6.0.26Steve Jonessteve@dkranch.net

RE: When to Use Dynamic SQL

acotgreave — Mon, 04 Mar 2002 08:17:00 GMT

Does anyone have any problems printing this article out? I'm trying to print it using IE6, but with no joy. No print job is sent to the printer. Other articles from this site print fine. Strange!

RE: When to Use Dynamic SQL

Robert W Marda — Tue, 19 Feb 2002 10:12:00 GMT

sorry, meaning different databases with distinct but related purposes. One is our data on 13F filings (stocks), one is for our N30D filings (mutual funds), one for staff that buy stocks and manage mutual funds, one for staff that sell stocks. Analysts are in there somewhere too. And a few other databases.Robert Marda

RE: When to Use Dynamic SQL

Andy Warren — Tue, 19 Feb 2002 10:08:00 GMT

Different regions meaning different parts of a table?Andy

RE: When to Use Dynamic SQL

Robert W Marda — Tue, 19 Feb 2002 09:32:00 GMT

We have stored procedures that hit different regions in our database and when a large change comes along it requires changes to many of them. It would be fewer, but we have been converting stored procedures from dynamic SQL to static SQL and usually this means 1 stored procedure becomes more than 1. The major change was when 1 became 22.I wish we could plan ahead for the changes, but often we don't know until a month or 2 before it goes live. Sometimes we have a 3 or 4 month warning, but that is rare.Robert Marda

RE: When to Use Dynamic SQL

Andy Warren — Tue, 19 Feb 2002 07:06:00 GMT

Seems like a function would work for that?Andy

RE: When to Use Dynamic SQL

nigelrivett — Tue, 19 Feb 2002 05:52:00 GMT

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 outputAnd 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.

RE: When to Use Dynamic SQL

Andy Warren — Tue, 19 Feb 2002 04:56:00 GMT

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

RE: When to Use Dynamic SQL

Robert W Marda — Tue, 19 Feb 2002 04:06:00 GMT

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

RE: When to Use Dynamic SQL

Andy Warren — Sun, 17 Feb 2002 06:55:00 GMT

I removed the duplicate postings you mentioned.Why would your procs change so often? And in what way do they change?Andy

RE: When to Use Dynamic SQL

Robert W Marda — Sat, 16 Feb 2002 18:05:00 GMT

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

RE: When to Use Dynamic SQL

Steve Jones - SSC Editor — Tue, 12 Feb 2002 12:31:00 GMT

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 Jonessteve@dkranch.net

RE: When to Use Dynamic SQL

K. Brian Kelley — Mon, 11 Feb 2002 08:29:00 GMT

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 Kelleybkelley@sqlservercentral.comhttp://www.sqlservercentral.com/columnists/bkelley/

RE: When to Use Dynamic SQL

Antares686 — Mon, 11 Feb 2002 07:23:00 GMT

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.

RE: When to Use Dynamic SQL

Robert W Marda — Mon, 11 Feb 2002 07:04:00 GMT

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

RE: When to Use Dynamic SQL

Antares686 — Mon, 11 Feb 2002 06:59:00 GMT

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.

RE: When to Use Dynamic SQL

Robert W Marda — Mon, 11 Feb 2002 06:56:00 GMT

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

RE: When to Use Dynamic SQL

Robert W Marda — Mon, 11 Feb 2002 06:52:00 GMT

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

RE: When to Use Dynamic SQL

Andy Warren — Mon, 11 Feb 2002 05:43:00 GMT

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

When to Use Dynamic SQL

Robert W Marda — Sun, 10 Feb 2002 00:00:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/rmarda/whendynamicsqlisuseful.asp