Here’s something I wrote on SSC a few yrs ago and I thought I’d rewrite it here to make sure it’s fresh on everyone’s mind.
One of the things I hate the most about dynamic SQL is all those single quotes you have to count when building strings. I’m a DBA so we’re not very bright to begin with, and keeping track of the piles of single quotes makes my head spin. And forget about trying to insert something in the middle of all that garbage.
The example we’ll be working with today is a simple query that creates file move statements so you can move a DB with a lot of files to a new drive easily. I’ll throw in another component here in a minute.
So here’s the T-SQL we’ll be working with. It’s not as bad as some I’ve seen, but it’ll get the point across.
1 2 3 4
SELECT 'alter database CPY modify file (name = [' + NAME + '] , FILENAME = ''' + 'P:\' + NAME + '\' + physical_name + ''')' + 'GO' FROM sys.database_files
So you see here we’re stacking up these single quotes inside there so we can get the actual quotes printed in the resultset. Anybody who konws me knows I’m a big fan of code that writes code. And everything doesn’t have to be fully automated. There’s really something to be said for scripting the code generatin and then making minor changes as needed, or just running it by hand. There are a few things I’d rather not run automatically, but just paste the code into the editor and run it manually. Something like this is one of those things. So anyway, let’s go about getting rid of all those single quotes that are stacked up inside there.
1 2 3 4 5 6 7
Declare @SQ char(1) Set @SQ = char(39) SELECT 'alter database BHCSECLPCPY modify file (name = [' + NAME + '] , FILENAME = ' + @SQ + 'P:\' + NAME + '\' + physical_name + @SQ + ')' + 'GO' FROM sys.database_files
Ok, explaining this a little. The magic happens in the 1st 2 lines. Set a var as a char(1), then set that equal to char(39).
in ASCII char(39) is a single quote. So now anywhere you have those double and triple quotes, just replace them with a @SQ. It’s much easier to see where your SQs are supposed to go, and much easier to add stuff in the middle of it.
Notice also that I put GO stmts at the end. It’s not necessary for this operation, but for some it is. And even when it’s not there are just some people who really like to see them in there. This is another one of those semi-automated processes I was talking about. If you want your GOs on a separate line, it just takes a little regex in SSMS. Start by pasting the results of the above query into a separate SSMS query window. Here’s what it’ll look like.
Now we’re just gonna do a little scrubbing in SSMS.
For this we’re going to choose regex (regular expressions) because they’re cool and super useful for these semi-automated functions.
So in your code window in SSMS hit ctrl+H to open the replace window.
Then make sure your settings look like this.
Now I’ll explain what’s important. You’re searching for the word GO, and replacing it with \nGO.
\n is the regex code for ‘new line’. There’s nothing more to it than that.
And one more thing for completion. If you wanna see what other ASCII chars there are, I’ve got a little script that’ll show you.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
/* Author: Sean McCown Date: 06/05/2003 Lists all char codes so you can find the code for the char you're looking for. Tells that char(39) is ', etc. Very nice to have around. */ Declare @Chars Table ( Code varchar(10), Char varchar(4) ) Declare @i int Set @i = 0 While @i < 256 BEGIN Insert @Chars Select 'Char(' + cast(@i as varchar(4)) + ')', char(@i) Set @i = @i + 1 END Select * from @Chars
OK guys… that’s my little corner of the world today. It’s not widely useful but it does come in handy from time to time.
Incoming search terms:
- dynamic sql single quotes explained
The best database career advice you’ve never heard!
Become a DBA. Become a BETTER DBA. Use the Roadmap.
The DBA Roadmap Seminar is 7 MP3 tracks (over 5 hours!) of insider guidance on your database career. We'll teach you how and what to study as a DBA, weigh in on controversial resume debates, teach you to recognize a worthy recruiter, and discuss the new professionalism of interviews. Also some bonus materials, PDF companion guides, and really spiffy intro music!
Once your $99 PayPal payment is submitted, you'll get the download link in e-mail! (Download is a 370Mb ZIP file.)
Visit www.DBARoadmap.com for info, forums, and more!
TagsArchitecture blunders Camtasia Career Coding Standards CPU data generator DBA Development disaster firewall Idiots Inside SQL Server Interview ITBookworm Jobs Kalen Delaney Katmai Ken Henderson LiteSpeed MidnightDBA PASS Summit 2011 Pinnacle politics Powershell Powershell Hero Query techniques Red-Gate reporting restore SQL Express 1433 SCM SQL Server SQLServerDVD.com SSIS SSRS Training troubleshooting TSQL Tuesday tuning Tutorials Video post videos Yukon
Interview: Kalen Delaney!
- Reading: Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions by Itzik Ben-Gan
- Repost: “Problems with my new book” by Grant Fritchey
- Announcing SQL Server 2012 Query Performance Tuning by Grant Fritchey
- Quick Review: Logitech Trackball
- Book Review: Microsoft SQL Server 2008 Internals, by Kalen DeLaney et al