Get Away From Confusing Code

Quite often we're forced to write dynamic SQL for some specific process. One of the things I've always hated is how confusing it is to troubleshoot syntax errors when you start playing with single and double quotes. Is it three quotes here, or four, or is it two? You always have to reason it out and play with the code, and maybe it's not even in the spot you're working on.

What you're trying to avoid is code like this. This statement actually inserts the defrag statement itself into a #Table.

     insert #Defrag
       select 'DBCC DBReIndex(' + @Table + ',' + '''' + '''' + ', 80)'

This statement just has to have so many quotes because of the spaces in the col names. It could be done with [] also, but this is an example so just take it for what it's worth. I also just sketched this code out real quick, and I have no idea if it's actually correct… too many single quotes for me.

@sql = ('select col1 as ' + '''Hello Kitty''' + ', 
	col2 as  ' + '''Today is Today''' + ',
	col3 as ' + '''War is hell''' + 
'from table1')

OK, so we've all seen code like this, and even worse. Here's a simple solution that will make your live a lot easier when writing dynamic SQL. Use the ASCII chars instead. You can easily tell where your quotes are supposed to be and you won't have a problem. Here's what I mean. I'll rewrite the statements above with the ASCII chars instead and you'll see what I mean.

Declare @SQ char(1)
Set @SQ = char(39)

insert #Defrag
 select 'DBCC DBReIndex(' + @Table + ',' + @SQ + @SQ + ', 80)'

Char(39) is the ASCII code for a single quote. Therefore, whenever you need to use a single quote, instead of using the escape method, simply put in the var '@SQ' instead.

Here's the other statement.

@sql = ('select col1 as ' + @SQ + 'Hello Kitty' + @SQ + ', 
	col2 as  ' + @SQ + 'Today is Today' + @SQ + ',
	col3 as ' + @SQ + 'War is hell' + @SQ + 
'from table1')

Another couple examples:

… ' where LastName = ' + @SQ + @LName + @SQ
… 'where TDate = ' + @SQ + GetDate() + @SQ
… 'where LDate = ' + @SQ + GetDate() - 100 + @SQ

Anyway, you get the idea.

The thing is, this can be done with anything, even in protecting against SQL injection, so it's good to know. But how are you supposed to know what the ASCII chars are for the entire char set? Well, I've written a small loop that you can run to find the char you're looking for and use it wherever you like.

You can see it's very simple code, but very handy to have around if you write a lot of dynamic code.

/*
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

Enjoy and I'd welcome comments if you have any.