Dynamic Quote Problem

I'm trying to insert a quarter and year variable in CODE (BAD) section below
in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
look like CODE (GOOD) section. I'm having trouble getting the correct quotes
around the @.YearName and @.QtrName1 part in CODE (BAD) section.
Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
CODE (BAD)***********************************
**
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
declare @.QtrName1 int, @.QtrName2 int, @.YearName int
set @.QtrName1 = '1'
set @.QtrName2 = '2'
set @.YearName = '1997'
SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter,
'
SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID'
SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter,
'
SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID'
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
EXEC(@.SQL)
CODE (GOOD)**********************************
***
SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
UNION ALL
SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductIDScott wrote:

> I'm trying to insert a quarter and year variable in CODE (BAD) section bel
ow
> in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
> look like CODE (GOOD) section. I'm having trouble getting the correct quot
es
> around the @.YearName and @.QtrName1 part in CODE (BAD) section.
> Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
> CODE (BAD)***********************************
**
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
> declare @.QtrName1 int, @.QtrName2 int, @.YearName int
> set @.QtrName1 = '1'
> set @.QtrName2 = '2'
> set @.YearName = '1997'
> SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
> ''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter
,
> '
> SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
> ''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter
,
> '
> SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
> EXEC(@.SQL)
>
>
> CODE (GOOD)**********************************
***
> SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID
> UNION ALL
> SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
> Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID ON Products.ProductID = [Order Details].ProductID
You don't need dynamic SQL for any of that, so why do it? Extraneous
quotes etc removed from the following. If you quote it up again it
should work. It looks like you've left out WHERE clauses for the
quarters though.
SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
+ CAST(@.qtrname1 AS VARCHAR(55)) AS Quarter,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID
UNION ALL
SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
+ CAST(@.qtrname2 AS VARCHAR(55)) AS Quarter,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories
INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID
INNER JOIN [Order Details]
ON Products.ProductID = [Order Details].ProductID
INNER JOIN Orders
ON Orders.OrderID = [Order Details].OrderID ;
David Portas
SQL Server MVP
--|||Scott (sbailey@.mileslumber.com) writes:
> I'm trying to insert a quarter and year variable in CODE (BAD) section
> below in a dynamic sql statement from Northwind. I'm trying to make CODE
> (BAD) look like CODE (GOOD) section. I'm having trouble getting the
> correct quotes around the @.YearName and @.QtrName1 part in CODE (BAD)
> section.
Nah, getting order into nested quotes is something I leave as an exercise
to the poor student. :-)
But some hints:
1) The function quotename() can sometimes be handy.
2) If you use SET QUOTED_IDENTIFIER OFF, you can also use " as quote
delimiter. You cannot use this, if there indexed views involved, or
you need to use indexes on computed columns. But it does make
composition of SQL strings easier.
3) There is actually no obligation to do this in T-SQL. After all, building
SQL strings is about string manipulation, and that is not a strong
point of T-SQL. Doing in client code may be better.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> You don't need dynamic SQL for any of that, so why do it? Extraneous
> quotes etc removed from the following. If you quote it up again it
> should work. It looks like you've left out WHERE clauses for the
> quarters though.
David, has it never occurred to you that what you see may only be a piece
of the actual problem? Since I wrote a crosstab query for Scott earlier
this wend, I strongly suspect that his query will include a dynamic
number of UNION ALL things.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||it was a simple example without the where. thanks for the extra info.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns972A4E50E881Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Nah, getting order into nested quotes is something I leave as an exercise
> to the poor student. :-)
> But some hints:
> 1) The function quotename() can sometimes be handy.
> 2) If you use SET QUOTED_IDENTIFIER OFF, you can also use " as quote
> delimiter. You cannot use this, if there indexed views involved, or
> you need to use indexes on computed columns. But it does make
> composition of SQL strings easier.
> 3) There is actually no obligation to do this in T-SQL. After all,
> building
> SQL strings is about string manipulation, and that is not a strong
> point of T-SQL. Doing in client code may be better.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Scott,
A simple solution to this (which I give at the risk of
encouraging too much dynamic SQL, which can be
downright dangerous) is to use REPLACE, based on
a model query. It's quite easy to set up, and it avoids
most headaches.
declare @.SQL varchar(4000)
set @.SQL = '
SELECT
''Qtr $$@.YearName$$-$$@.QtrName1$$'' AS Qtr,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
UNION ALL
SELECT
''Qtr $$@.YearName$$-$$@.QtrName2$$'' AS Qtr,
COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity
FROM Categories INNER JOIN
Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
ON Products.ProductID = [Order Details].ProductID
'
set @.SQL = REPLACE(@.SQL,'$$@.YearName$$',@.YearName)
set @.SQL = REPLACE(@.SQL,'$$@.QtrName1$$',@.QtrName1)
set @.SQL = REPLACE(@.SQL,'$$@.QtrName2$$',@.QtrName2)
The only quotes you have to double in this case are the
few in the model query. If any of the substituted parameters
can contain quotes, you will have to do additional replacements
like
set @.param = replace(@.param,char(39),char(39)+char(39
))
Even other complications, like an unknown number of UNION ALL
clauses, are not so hard to handle this way.
Steve Kass
Drew University
Scott wrote:

>I'm trying to insert a quarter and year variable in CODE (BAD) section belo
w
>in a dynamic sql statement from Northwind. I'm trying to make CODE (BAD)
>look like CODE (GOOD) section. I'm having trouble getting the correct quote
s
>around the @.YearName and @.QtrName1 part in CODE (BAD) section.
>Can someone copy/paste CODE (BAD) section into Northwind and fix quotes?
>CODE (BAD)***********************************
**
>declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000)
>declare @.QtrName1 int, @.QtrName2 int, @.YearName int
>set @.QtrName1 = '1'
>set @.QtrName2 = '2'
>set @.YearName = '1997'
>SET @.SQL1 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
>''' + '''-''' + ''' + CAST(@.QtrName1 AS VARCHAR(55)) + ''' + ' AS Quarter,
>'
>SET @.SQL1 = @.SQL1 + 'COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID'
>SET @.SQL2 = 'SELECT ' + '''Qtr''' + ''' + CAST(@.YearName AS VARCHAR(55)) +
>''' + '''-''' + ''' + CAST(@.QtrName2 AS VARCHAR(55)) + ''' + ' AS Quarter,
>'
>SET @.SQL2 = @.SQL2 + 'COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID'
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2
> EXEC(@.SQL)
>
>
>CODE (GOOD)**********************************
***
>SELECT 'Qtr 1997-1' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
>FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID
>UNION ALL
>SELECT 'Qtr 1997-2' AS Qtr, COUNT(Orders.ShipName) AS ctShipName,
>COUNT(Orders.ShipCity) AS ctShipCity
>FROM Categories INNER JOIN
> Products ON Categories.CategoryID =
>Products.CategoryID INNER JOIN
> Orders INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
>Details].OrderID ON Products.ProductID = [Order Details].ProductID
>
>|||i realize now that i don't need dynamic sql for this satement, but for
learning reasons, what would be the correct syntax for the year and qtr
concatenation using dynamic sql be?
sql quotes are tricky.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134340924.362906.149210@.g14g2000cwa.googlegroups.com...
> Scott wrote:
>
>
> You don't need dynamic SQL for any of that, so why do it? Extraneous
> quotes etc removed from the following. If you quote it up again it
> should work. It looks like you've left out WHERE clauses for the
> quarters though.
> SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
> + CAST(@.qtrname1 AS VARCHAR(55)) AS Quarter,
> COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories
> INNER JOIN Products
> ON Categories.CategoryID = Products.CategoryID
> INNER JOIN [Order Details]
> ON Products.ProductID = [Order Details].ProductID
> INNER JOIN Orders
> ON Orders.OrderID = [Order Details].OrderID
> UNION ALL
> SELECT 'Qtr' + CAST(@.yearname AS VARCHAR(55)) + '-'
> + CAST(@.qtrname2 AS VARCHAR(55)) AS Quarter,
> COUNT(Orders.ShipName) AS ctShipName,
> COUNT(Orders.ShipCity) AS ctShipCity
> FROM Categories
> INNER JOIN Products
> ON Categories.CategoryID = Products.CategoryID
> INNER JOIN [Order Details]
> ON Products.ProductID = [Order Details].ProductID
> INNER JOIN Orders
> ON Orders.OrderID = [Order Details].OrderID ;
> --
> David Portas
> SQL Server MVP
> --
>|||Erland Sommarskog wrote:
> David, has it never occurred to you that what you see may only be a piece
> of the actual problem? Since I wrote a crosstab query for Scott earlier
> this wend, I strongly suspect that his query will include a dynamic
> number of UNION ALL things.
>
It occurred to me, that's why I asked.
David Portas
SQL Server MVP
--|||Scott (sbailey@.mileslumber.com) writes:
> i realize now that i don't need dynamic sql for this satement, but for
> learning reasons, what would be the correct syntax for the year and qtr
> concatenation using dynamic sql be?
> sql quotes are tricky.
Nah, just overwhelming the more you nest. The basic rule is simple: any
nested quote needs to be doubled.
In addition to everything else, the syntax colouring in Query Analyzer is
helpful here. If an expression like CAST(@.YearName AS VARCHAR(55)) comes out
read, you are an odd number of quotes.
If it is a consolation, also experienced SQL programmers like me have
to fight a battle with all the '. It was oh so easier back in the days
I could use " as well. So that's the real story I don't post any example -
I probably get it wrong. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql