|
Comments
|
|
Thanks, Jack! Is the example available for download?
|
|
|
It was very good...
|
|
|
Well presented. I like the format.
I'll need to determing an application for it and come back to see it again.
|
|
|
This presentation uses a better, larger font for the SQL code that is customary.
The presenter should show the timing differences for cache hits and misses.
|
|
Marc Moshman on
1/11/2010
Very good method. I've used this and had a difficulty in rippling errorcodes back when sp_executesql called another stored procedure which had an error occur.
|
|
|
Excellent overview… thanks
|
|
|
Very useful information overall. In my Firefox browser, the video lagged behind the narration too much to follow. Also suggest slightly better audio recording settings to reduce distracting digital decompression artifacts. Thanks, JH
|
|
|
skimmed over too much
|
|
|
Tried to use this with SQL 2005, but apparently the schema for AdventureWorks has changed... sigh.
|
|
|
It would be good to go more in depth based on when to allow the query plans to be based on parameters and when to allow them to be based on the entire query
|
|
|
Simple basic and to the point. Nothing extra.
|
|
|
If you take the time to construct your SQL queries, you can do this with out SQL on the fly. It's not that difficult.
|
|
|
Why promote poor SQL design. Anytime someone thinks they have to use SQL on the fly, they are just not taking the time to think things through. In just about every instance where SQL on the fly was used, it could have been written with standard SQL clauses.
|
|
Peter Voutov on
1/11/2010
Well done.
If you're looking to cut back on cache hits you can build a query that includes both parameters:
(@DynamicFirstName is null or FirstName like N'%' + @DynamicFirstName)
... similar statement for the @DynamicLastName...
This way both parameters are in your query and the null condition is handled. This also eliminates the need for conditional if statements and the where 1=1 trick.
|
|
Peter Voutov on
1/11/2010
In response to Angelo Cook above.
He's not promoting poor SQL design, just explaining functionality any good DBA should know about.
I agree the example isn't a good one, because the query can be structured without using dynamic SQL.
But there are tasks that need this (useless you want to write code outside the database).
For example, I was recently asked to perform a data transformation task, which involved transposing origin data (take columns X, Y, Z --> create table with rows X, Y, Z). I was able to use information_schema.syscolums to build dynamic SQL which accomplished this and sp_executeSQL came in quite handy.
|
|
Jack Corbett on
1/12/2010
Rich,
The code is not posted anywhere , but if you check out this entry on my blog, http://wiseman-wiseguy.blogspot.com/2009/10/maintaining-security-and-performance.html, I do a similar thing with the AdventureWorks 2005 database and also do some comparison between dynamic SQL and static SQL. This blog post will also answer Chris and Angelo's questions/comments.
|
|
Jack Corbett on
1/12/2010
Robert,
When limited to 5 minutes it is difficult to go in depth on any topic. I would have liked to have spent time on SQL Injection, security, comparison with EXEC(@sql), but barely got this much in in 5 minutes.
|
|
Jack Corbett on
1/12/2010
Chris,
Yes the schema for AdventureWorks changed quite a bit between 2005 and 2008. In my first comment I posted a link to my blog where I do a similar thing, somewhat more in-depth using AdventureWorks 2005 and the code for that is available for download.
|
|
Jack Corbett on
1/12/2010
Angelo,
Thanks for the comments. Yes, I also try to avoid using Dynamic SQL as much as possible, but in some cases, such as a multi-parameter search where the parameters can be null, dynamic SQL can be the best way to do it. I only presented a very simple case here with 2 parameters, but if there are 8 or 10 then your SQL can get pretty ugly with all the IF tests. If you check out the blog post I provided in my first comment you'll see the same example also compared with some other options for this case and my explanation as to why I'd use dynamic SQL for search.
|
|
|
Really liked it. Only suggestion is that there was too much repetition about cache hits and misses, and not enough info about the syntax of sp_executesql
|
|
|
Wow, I've been using the "Exec (sql)" method -- with this info, I'm switching!
|
|
|
good
|
|
Lonnie Meinke on
2/10/2010
A bit too much for a single video. Break into 2 and go more in depth.
|
|
Jack Corbett on
2/11/2010
Lonnie,
How would you have broken it out? You also have to remember that the purpose of these videos is to be short how-to videos, not in-depth explanations.
|
|
|
isn't it fast for non English natives
|
|
Jack Corbett on
3/26/2010
ShaheedulHaq,
Sorry it seems a bit fast. I did try to slow myself down a little bit.
|
|
|
Nice presentation.
|
|
|
it would have been better if there was some info provided about the various issues associated with concatenating strings in dynamic sql. I always have trouble with the quotes.
|
|
Rebecca Scott on
3/11/2011
Excellent Video! Really like the suggestion of separating out the SQL into different parts...never thought of doing that before.
|