Dynamic SQL Doesn’t Have To Be That Hard

Published On: 2017-08-21By:

sign that says dynamic on itThere’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.

Denny

 

The post Dynamic SQL Doesn’t Have To Be That Hard appeared first on SQL Server with Mr. Denny.

I really hope that Zuckerberg doesn’t run for President

Published On: 2017-08-16By:

zuckerberg tweeting he might run for POTUSLast week Mark Zuckerberg made a couple of astonishing announcements.  The first is that he’s no longer an atheist.  The second is the one that’s a little more important to the rest of the world, that he believes “running for president [is] very important”.

Now I can’t tell you how opposed to this I am.  So much so that I’m going to talk about politics on my technology blog, knowing that it’ll be picked up by my companies websites (among others).

Zuckerberg is completely unqualified to be the President of the United States that we live in today.  He’s had one job, where he’s the boss.  He has no work experience outside of people doing whatever he tells them to.  He has no experience in any sort of public service or government.

He’s started a non-profit to help with education, where he’s still the boss and things will still be done his way.

He’s go no idea how to negotiate for the betterment of others. He’s got no idea how to negotiate anything that we know of, as he’s got people for that.

He’s basically still in his 20s but with unlimited money, and being President seems like a nice thing to do to impress the wife’s parents.

We currently have a President with no political experience, who’s never had a job where he wasn’t the boss (and I’d argue never had a job at all) and who has no sense of reality or real life. Remember a couple of months ago when Zuckerberg went on tour all around the US to learn what “normal” people did?  If that wasn’t an attempt to look more like a normal guy, getting his picture taken on a tractor for example, I don’t know what is.

If Zuckerberg wants to do something useful in the next presidential election, he should give a billion or two to whatever liberal candidate he wants to support (I’m assuming he’s a liberal as he was atheist and now he’s apparently Buddhist according to one article I read) and leave the politics to the professionals. We’re trying this whole let someone who’s a business man (I’d argue that Zuckerberg isn’t a business man) be the President and it’s going about as expected.

Denny

The post I really hope that Zuckerberg doesn’t run for President appeared first on SQL Server with Mr. Denny.

Time for a Data Platform Summit PreCon

Published On: 2017-08-06By:

Well it’s time for another great Data Platform Summit in Bangalore India.  The team there has been kind enough to allow me to present a pre-con this year (third year in a row).  This year I’ll be changing up my precon a little bit. Instead of talking about SQL Server, I’ll be focusing more on Azure and everything that the DBA that’s going to be moving into Azure needs to know about Azure (or at least as much as I can cram into a 7 hour session, I’ve got 4 days worth of material I can present but only 7 hours to present it).Denny is presenting a precon session in India

During the session this year we’ll be learning all about the components that you need to get know about  and setup to successfully run a high volume SQL Server in a VM in the Azure Cloud.

This session is really important to the Indian market specifically as Microsoft has just opened new Azure data centers in India in the last year, so more and more Indian companies should be expecting to move workloads to Azure.  This session will give attendees the knowledge to become the experts within the organizations to be able to help their companies move into the Azure Cloud.

So what do you do now?  You go signup for the all day PreCon session and then come to the session. Nice and easy.

Denny

The post Time for a Data Platform Summit PreCon appeared first on SQL Server with Mr. Denny.

PASS 2017 Attendee Orientation

Published On: 2017-08-04By:

Join me on September 14th, 2017 for my annual PASS Summit Attendee Orientation webcastLearn more about Chris singing at attendee orientation.,. Like in prior years this is the must see pre-PASS Summit webcast were you’ll get all sorts of information about the PASS Summit for this year. This includes sessions, directions around the convention center, parties, Speaker Idol, and much, much more.

Getting registered for the webcast is simple.  Go here and fill this out.  That’ll get you the link to the URL for the webcast and the ICS file to put into Outlook so that you get a reminder for the webcast.

There will be reminders posted on Social Media, but even if you haven’t gotten approval to go to PASS yet, get registered for this webcast so you don’t forget. After all, it’s free.

Denny

The post PASS 2017 Attendee Orientation appeared first on SQL Server with Mr. Denny.

1 2 3 315

Video

Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.