There’s a lot of misunderstandings about dynamic SQL out there. I have heard this from clients on occasion, and I see it from other consultants on their blogs. If done correctly, dynamic SQL is not difficult to use or troubleshoot. Let’s look though some of the complaints that I’ve seen.
If you’re using NVARCHAR strings, you need to prefix all of your string concatenations with N, or you could end up silently truncating your final product.
Yes, if you are using NVARCHAR you do need to prefix everything with N so that SQL Server knows that all your strings are Unicode. There is no risk of truncate your final product. If you forget to put the N in front of a string when concatenating strings together, the only problem you will have is that a question mark where will appear you should have a Unicode character. You won’t suddenly end up with a shorter string than you were expecting.
If you use CASE to branch concatenation paths, it can also silently truncate a string.
Yes, CASE can do this, if you don’t put an ELSE block in your CASE expression. If you don’t have the ELSE block in your CASE expression, then your code could return NULL from CASE. Unless you’re changed your CONCAT_NULL_YIELDS_NULL setting from the default, this will return a NULL value, which is the expected result. I am sorry, but you don’t get to complain that SQL Server is doing what you told it do. Adding an ELSE block that returns a blank string so that your statement looks like CASE WHEN … ELSE ” END isn’t all that hard.
Figuring out how many single quotes you need to properly quote things is awful, sometimes.
Generally speaking, once you are in a dynamic string instead of a single quote you just need two single quotes for each single quote that you want. Unless you are using dynamic SQL to generate dynamic SQL it shouldn’t be that hard. If you are using dynamic SQL to generate more dynamic SQL (which I’ve done) then you’ll want to stab yourself in the eye.
PRINT is limited to 8000 characters, RAISERROR even fewer.
Both of these statements are true. Keep in mind that PRINT shouldn’t be used to return information to a user; it just returns it to the messages tab in SSMS. In an application that output from PRINT isn’t displayed to the user at all (even though it is sent to the client, so it still has to travel over the network). If you need to get a warning or error to the user RAISERROR would be the way to do that, and 2048 characters should be enough space to do that in, as that’s a lot of text to send back to a user. Especially as most users won’t even read the error message. THROW, which was introduced in SQL 2012, has the same size limitation as RAISERROR.
If you’re using sp_executesql, it’s a real chore to get the variables as the query ran without additional logging.
Passing variables into sp_executesql is pretty straight forward. You simply declare than as an input parameter, which is the second parameter for sp_executesql. Then you pass them in as named parameters to sp_executesql and use then within the dynamic SQL as needed. The nice thing about dynamic SQL is that your can pass in parameters that you aren’t even using, so if you remove parts of the code that use the parameter you can still pass it in to the dynamic SQL and SQL won’t care. As for not being able to log what parameters are being passed in to the query, who cares? Logging the queries that are passed in introduces so many potential security issues not the least of which may include storing PII information in an unencrypted format that I’d never recommended doing this. Not to mention that this is adding additional overhead and risk to the system for not useful benefit. If you need to see how the query procedure dealt with the variables before running the dynamic SQL then
Concatenating non-string data requires CAST/CONVERT.
Yeah, this is a real pain in a strongly typed language. Just like in C, C++, C#, etc. Also stop stringing non-text fields together. You simply pass them into dynamic SQL as variables and handle like you would with normal variables. There are very few cases when I’d want to handle dates and/or numbers (as an example) in this way.
Sometimes surprise NULL concatenations leave you with empty strings.
Yeah, that happens when you concatenate NULL and something together in SQL Server. This is by design. If you don’t want this check for NULL using ISNULL, COLLASE, CASE or change the CONCAT_NULL_YIELDS_NULL setting.
In summary, I’d have to say that Dynamic SQL really isn’t all that hard to work with. If you remember the rules of strings and NULLs then working with dynamic SQL isn’t pretty straight forward. Dynamic SQL makes lots of sense to use in order to reduce the complexity of SQL Statements, and simple SQL statements are easier for SQL to execute.
The post Dynamic SQL Doesn’t Have To Be That Hard appeared first on SQL Server with Mr. Denny.
Very good write up on the dynamic SQL topic. Few things you might want to add. If ones dynamic SQL query is to include an OPENROWSET and/or OPENQUERY, single quotes issue would cause an allergic reaction. Additionally, you might want to add CONCAT function to the list of tools to concatenate strings and deal with NULLs.