SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 why so many single quotes ' in dynamic sql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/23/2012 :  09:20:13  Show Profile  Reply with Quote
hi,

I just started learning dynamic sql from free online sources. I'm confused with the multiple quotes on those examples.

Is there any logic behind those quotes?

Here is one example :

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)


Why have they put three quotes in London?

bitsmed
Constraint Violating Yak Guru

433 Posts

Posted - 09/23/2012 :  10:11:03  Show Profile  Reply with Quote
quote:
Originally posted by learning_grsql

hi,

I just started learning dynamic sql from free online sources. I'm confused with the multiple quotes on those examples.

Is there any logic behind those quotes?

Here is one example :

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)


Why have they put three quotes in London?


When inside the string definition, two single quotes will convert to one single quote.
Lets say you want a string to hold the value I am a sql wiz, you'd just type:
SET @description='I am a sql wiz'

Now, if you want the string to hold I'm a sql wiz, you'd type:
SET @description='I''m a sql wiz'

In the example above:
Red quotes are start/stop quotes for a given string.
Green quotes are inside the given string, and will thus convert to a single quote.

So in essence, the city variable in your example, is set to 'London' in order for the select statement to work, as you would write:
SELECT CustomerID, ContactName, City FROM customers WHERE City = 'London'

Hope that you understand, if not let me know, and I'll explain another way.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/23/2012 :  19:44:19  Show Profile  Reply with Quote
see

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

learning_grsql
Posting Yak Master

230 Posts

Posted - 09/24/2012 :  03:33:42  Show Profile  Reply with Quote
Many Thanks bitsmed and Visakh.

Now I feel like I have understood quotes completely.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 09/24/2012 :  13:04:28  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000