need guide or tutorial for string delimiters...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

need guide or tutorial for string delimiters...

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-26 : 12:51:36

I'm looking for a guide or tutorial on character string delimiters and concatenation within dynamic SQL. I'm perplexed about the syntax such as how many apostrophe's, when to use the "+". I've also noticed that leaving a space between two apostrophe's has produced syntax errors which irked me. Take a gander at this:

SET @Clause = 'Data=''Check'' And Data2=''Check2'''

exec('Select * from table1 where ' + @Clause + '')

It took me time to get the above to work but it does work. I don't completely understand why the exec statement works, though. I know the outside delimiters (I'll label them ['] to make it readable) enclose the entire select statement. But why wouldn't the following work:

exec('Select * from table1 where ' + @Clause') ?

Why do I need the "+" and ['] after @Clause?

Actually, is the ['] after the where used to "close" the previous string or is it used to "open" + @Clause +?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-26 : 12:56:26

This is exactly why you should always PRINT @variableName before running the EXEC. This way you can see exactly where the problem is and where you need add or remove ticks.

So always do this instead:

SET @variableName = ...

PRINT @variableName

EXEC (@variableName)

And you need the + as that's how you concatenate strings together. You do not need ending ticks after a variable if you have properly put them inside the variable already.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-26 : 13:19:23

Also, if you use PRINT you can just stick the output into your favour SQL tool and try it - once you have debugged it you can make those changes back in your code.


BEGIN TRANSACTION

... your PRINT'd SQL statement here ...

ROLLBACK

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-29 : 03:25:07

www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Koji Matsumura
Posting Yak Master

141 Posts

Posted - 2007-10-29 : 03:44:29

If
exec('Select * from table1 where ' + @Clause + '')
works then
exec('Select * from table1 where ' + @Clause)
should work also because + '' is not adding anything.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources