June 14, 2010 at 11:28 am
Hello Everyone
I am trying to gather some information about the use of Dynamic SQL, either from a front-end or in a sproc.
I personally, never ever use Dynamic SQL in anything. I always call a sproc.
But even inside a sproc, you can have dynamic SQL.
I am just trying to find a way to put into words when telling someone else, why you should not. Or I should say, This is the reason why we should or should not. I do not want to make it sound like "it is my way, or the highway" type of thing.
So please tell me, why you think that one should use or not use dynamic SQL in a sproc.
Thanks
Andrew SQLDBA
June 14, 2010 at 11:43 am
Dynamic SQL allows you to write queries that have the ability to write queries for you.
Having a query that generates queries for you usually saves lots of time during development and maintenance.
Usually a Dynamic SQL statement includes two kinds of components, some static code (what you already know is needed at the time you write the query) and some dynamic code and/or variables that will be added as needed (what you don't know until the query is running).
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.June 14, 2010 at 11:54 am
Agreed.
On the other hand, dynamic sproc does not have a history of executions and therefore cannot be optimized. That makes dynamic SQL less suitable for high performance systems.
June 14, 2010 at 12:55 pm
You can optimise dynamic sql as everything else.
Dynamic sql is absolutely legitimate way of coding and it has own place in design and development of SQLServer based systems.
As everything else, it does require some knowlege of good practices (eg. use sp_executesql with passing parameters to prevent code injection).
June 14, 2010 at 1:00 pm
I used dynamic SQL (within an SP) not long ago to resolve an issue I had. I was trying to use a WHERE clause, and couldn't get it to work correctly. Dynamic SQL ended up being the resolution to my problem.
If you're interested, here's the link to my thread.
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/
June 14, 2010 at 1:04 pm
June 14, 2010 at 1:05 pm
Sorry for the messy post -- I should have previewed it.
June 14, 2010 at 1:21 pm
Revenant (6/14/2010)
Sorry for the messy post -- I should have previewed it.
You can go back and edit it and make it, well, unmessy.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 14, 2010 at 1:28 pm
Good article, I've seen it before.
But where it said that the Dynamic SQL cannot be optimised?
June 14, 2010 at 1:33 pm
In general, I'd recommend against using dynamic SQL. That said, there are some very good situations where you should use it because it solves problems like dynamic search or other issues. The key would be to use it appropriately because, frankly, innapropriate use of dynamic sQL is what leads to insertion attacks. Look up the tale of little Bobby Tables for a perfect reference.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 14, 2010 at 1:36 pm
The biggest issue with dynamic SQL (ds) is security. For ds, the executor must have direct permissions to run the DML (/DDL) in the dynamic string; that is, the permission cannot come solely from a role. If you try to use role-based security -- and you probably should -- ds will not work properly.
Ds is also prone to injection attacks.
In general you should keep try to keep ds solely for internal code that never interacts in any way with a non-administrator/programmer.
For example, a dba or developer using ds to generate other code is great.
A developer using it from a web page to render results to a user is risky, especially since it usually involves the user providing some input values used in the ds. You must review all the security implications carefully.
In theory you can say it's ok in the early development stages as long as it's replaced by more robust methods -- stored procs, etc. -- before final deployment. But the problem is, "later on" never comes, and the ds is left in the app basically forever.
Scott Pletcher, SQL Server MVP 2008-2010
June 14, 2010 at 1:36 pm
AndrewSQLDBA (6/14/2010)
Hello Everyone...
So please tell me, why you think that one should use or not use dynamic SQL in a sproc.
...
If one knows what Dynamic SQL is for and how to use it properly - It is absolutely fine to use it.
If one knows nothing about how Dynamic SQL should be used - he should learn first, then he can make right decision to use it or not.
If one knows nothing about Dynamic SQL and doesn't want to know about it - it is also fine 😀
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply