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

Dynamic For Loop

My below code returns a union fine in northwind. However, I'd like add some
FOR LOOP's while building the SELECT statements to cut down on the redundant
code. As you can see, each @.SQLx statement has a variable from 1 to 3. I
created this exmple to get an idea of using a FOR LOOP to cut down on code.
CODE:
declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000), @.SQL3
varchar(4000)
declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
@.dtEndDate1 datetime
declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
datetime, @.dtEndDate3 datetime
declare @.typeID int, @.debug int
set @.typeID = '1'
set @.dtStartDate1='19960101'
set @.dtEndDate1='19961231'
set @.dtStartDate2='19970101'
set @.dtEndDate2='19971231'
set @.dtStartDate3='19980101'
set @.dtEndDate3='19981231'
set @.dtYear1='1996'
set @.dtYear2='1997'
set @.dtYear3='1998'
SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
set @.debug = 0
IF @.typeID = 1
BEGIN
SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
END
IF @.typeID = 2
BEGIN
SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
END
SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
Customers.CustomerID = Orders.CustomerID WHERE '
SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate1, 112) + ''''
SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate2, 112) + ''''
SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
@.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
convert(char(8), @.dtEndDate3, 112) + ''''
SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
IF @.debug = 1
PRINT @.SQL
ELSE
EXEC(@.SQL)Scott
SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
See soem examples
DECLARE @.i INT
SET @.i=1
WHILE @.i<100
BEGIN
INSERT INTO Table VALUES (@.i)
SET @.i=@.i+1
END
"Scott" <sbailey@.mileslumber.com> wrote in message
news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
> My below code returns a union fine in northwind. However, I'd like add
> some FOR LOOP's while building the SELECT statements to cut down on the
> redundant code. As you can see, each @.SQLx statement has a variable from 1
> to 3. I created this exmple to get an idea of using a FOR LOOP to cut down
> on code.
> CODE:
> declare @.SQL varchar(4000), @.SQL1 varchar(4000), @.SQL2 varchar(4000),
> @.SQL3 varchar(4000)
> declare @.dtYear1 int, @.dtYear2 int, @.dtYear3 int, @.dtStartDate1 datetime,
> @.dtEndDate1 datetime
> declare @.dtStartDate2 datetime, @.dtEndDate2 datetime, @.dtStartDate3
> datetime, @.dtEndDate3 datetime
> declare @.typeID int, @.debug int
> set @.typeID = '1'
> set @.dtStartDate1='19960101'
> set @.dtEndDate1='19961231'
> set @.dtStartDate2='19970101'
> set @.dtEndDate2='19971231'
> set @.dtStartDate3='19980101'
> set @.dtEndDate3='19981231'
> set @.dtYear1='1996'
> set @.dtYear2='1997'
> set @.dtYear3='1998'
> SET @.SQL1 = ' SELECT ' + '''Year $$@.dtYear1$$''' + ' AS Date, '
> SET @.SQL2 = ' SELECT ' + '''Year $$@.dtYear2$$''' + ' AS Date, '
> SET @.SQL3 = ' SELECT ' + '''Year $$@.dtYear3$$''' + ' AS Date, '
> set @.SQL1 = REPLACE(@.SQL1,'$$@.dtYear1$$',@.dtYear1)
> set @.SQL2 = REPLACE(@.SQL2,'$$@.dtYear2$$',@.dtYear2)
> set @.SQL3 = REPLACE(@.SQL3,'$$@.dtYear3$$',@.dtYear3)
> set @.debug = 0
> IF @.typeID = 1
> BEGIN
> SET @.SQL1 = @.SQL1 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL2 = @.SQL2 + 'MAX(Orders.Freight) AS MaxOfFreight '
> SET @.SQL3 = @.SQL3 + 'MAX(Orders.Freight) AS MaxOfFreight '
> END
> IF @.typeID = 2
> BEGIN
> SET @.SQL1 = @.SQL1 + 'COUNT(*) AS SalesCount '
> SET @.SQL2 = @.SQL2 + 'COUNT(*) AS SalesCount '
> SET @.SQL3 = @.SQL3 + 'COUNT(*) AS SalesCount '
> END
> SET @.SQL1 = @.SQL1 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL2 = @.SQL2 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL3 = @.SQL3 + 'FROM Customers INNER JOIN Orders ON
> Customers.CustomerID = Orders.CustomerID WHERE '
> SET @.SQL1 = @.SQL1 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate1, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate1, 112) + ''''
> SET @.SQL2 = @.SQL2 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate2, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate2, 112) + ''''
> SET @.SQL3 = @.SQL3 + 'Orders.OrderDate >= ''' + convert(char(8),
> @.dtStartDate3, 112) + '''' + ' AND ' + '' + 'Orders.OrderDate <= ''' +
> convert(char(8), @.dtEndDate3, 112) + ''''
> SET @.SQL = @.SQL1 + ' UNION ALL ' + @.SQL2 + ' UNION ALL ' + @.SQL3
> IF @.debug = 1
> PRINT @.SQL
> ELSE
> EXEC(@.SQL)
>|||A better way would be something like this:
SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
MAX(O.Freight) AS maxoffreight
FROM Customers AS C
JOIN Orders AS O
ON C.customerid = O.customerid
WHERE O.orderdate BETWEEN @.startdate AND @.enddate
GROUP BY YEAR(O.orderdate) ;
David Portas
SQL Server MVP
--|||I guess what I'm asking is there a way in SQL to create dynamic variables?
So for example, my code would loop 3 times and each time my variables like
@.dtStartDate1 would increase from @.dtStartDate1 to @.dtStartDate2 and then
@.dtStartDate3.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23aBO7zFBGHA.2512@.TK2MSFTNGP09.phx.gbl...
> Scott
> SQL Server does not have FOR LOOP , bit it does have WHILE LOOP
> See soem examples
> DECLARE @.i INT
> SET @.i=1
> WHILE @.i<100
> BEGIN
> INSERT INTO Table VALUES (@.i)
> SET @.i=@.i+1
> END
>
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:eR6CT9EBGHA.272@.TK2MSFTNGP09.phx.gbl...
>|||I would do it like that except we have custom year begin and end dates and i
didn't want to take time to write a custom function.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1134997569.867513.55250@.f14g2000cwb.googlegroups.com...
>A better way would be something like this:
> SELECT 'Year '+DATENAME(YEAR,MIN(orderdate)) AS [date],
> MAX(O.Freight) AS maxoffreight
> FROM Customers AS C
> JOIN Orders AS O
> ON C.customerid = O.customerid
> WHERE O.orderdate BETWEEN @.startdate AND @.enddate
> GROUP BY YEAR(O.orderdate) ;
> --
> David Portas
> SQL Server MVP
> --
>|||scott wrote:
> I would do it like that except we have custom year begin and end dates and
i
> didn't want to take time to write a custom function.
>
Are your annual begin and end dates fixed in the Calendar year or are
they based on some specific non-Gregorian calendar rules? If it's a
Calendar year, just use DATEADD to offset the date by the required
number of days. If it's specific company calendar logic, create a
calendar table and populate it with the information about your
customer's calendar. Join the calendar table into the query. Either
way, you don't need dynamic SQL.
David Portas
SQL Server MVP
--