Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get Away From Confusing Code

By Sean McCown,

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.

Total article views: 8778 | Views in the last 30 days: 6
 
Related Articles
FORUM

Blocking Issue:Insert blocking select statements

Insert blocking select statements

FORUM

Insert - Exec Select statements

Insert - Exec Select statements

FORUM

select statement

select statement

FORUM

Using the recordset from SELECT statement in an INSERT statement, all in one Stored Procedure

Using the recordset from SELECT statement in an INSERT statement, all in one Stored Procedure

FORUM

select statement

select statement with case

Tags
advanced querying    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones