Dynamic Sql

Question

Dynamic Sql

bryan99y

SSCarpal Tunnel

Points: 4672
More actions
October 16, 2002 at 10:46 am

#64197

Why do developers use dynamic sql?
What are the bad points?
- especially in terms of performance.
Use this example:
-- construct sql statement
SET @SQL = "set nocount on"
SET @SQL = @SQL + " SELECT"
SET @SQL = @SQL + " B.PolicyID AS lngPCYWorksheetPolicyID"
+ ","SET @SQL = @SQL + " B.WorksheetInstanceID AS lngPCYWorksheetWorksheetInstanceID"
+ ","SET @SQL = @SQL + " A.GenerationVersionNumber AS lngPCYDocumentGenerationVersionNumber"
SET @SQL = @SQL + " FROM PCY_Document A, PCY_Worksheet B "
SET @SQLWhereClause = " AND A.WorksheetInstanceID = B.WorksheetInstanceID "
etc........

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

Dynamic SQL runs in a separate batch than the calling batch. The implications are:

(1) You lose the advantage of ownership chains. This requires additional security checks. Also, it requires you to give permissions to the base tables, instead of controlling security through the stored procedures.

(2) If the dynamic SQL isn't easily parameterized, SQL Server may be required to generate a new execution plan each time. SQL 2K handles this better, but it's still a possibility.

These are two quick notes. You may also check out the articles Robert Marda has written on Dynamic SQL. He's written several and they do a good job of discussing the pros and cons. You can find them at http://www.sqlservercentral.com/columnists/rmarda/

HTH.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Simon Sabin SSCrazy Eights Points: 8142 More actions · Answer 2

Apart from the security issue dynamic SQL is not bad if it is written using parameterisation (sp_executesql).

It is the easiest way to perform complex searching where the search conditions are spread across columns and tables and are optional (bad design).

When I first started I the users wanted real power of searching, I said yes its possible, now with experience it is the one area that I believe DBAs should dictate what is possible. (rant over)

Simon Sabin

Co-author of SQL Server 2000 XML Distilled

http://www.amazon.co.uk/exec/obidos/ASIN/1904347088

Simon Sabin
SQL Server MVP

http://sqlblogcasts.com/blogs/simons

Robert W Marda SSChampion Points: 13413 More actions · Answer 3

I have been an SQL developer for about two years now. When I was first introduced to dynamic SQL I thought it was the coolest thing that you could do with SQL. Later, I learned that it was not as cool as I initially thought.

I know that in many cases it is quicker to develop a query using dynamic SQL. Some database people believe it is easier to maintain because to do the same thing with static SQL would require 10, 20, or more stored procedures.

I have converted one stored procedure from dynamic SQL to about 22 static stored procedures. I have found that the maintenance isn't that much more than the one SP and this group of SP's is by far faster and more efficient than the dynamic (even though the final query in the one dynamic is basically the same as one of the 22 static SP's.).

Also I have found that once you learn how to use dynamic SQL then anything you don't know how to do with static you just simply do with the dynamic. I now know that the majority of things done with dynamic SQL can be done as well if not better with static SQL.

Robert W. Marda

SQL Programmer

bigdough.com

The world’s leading capital markets contact database and software platform.

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