Dynamic SQL Concatenation

In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
However, in FIGURE 1, I'm trying to add a WHERE subquery that should return
FIGURE 2's results but only for the last 10 days. I'm getting a strange
error in QA. Can someone help me with my syntax in FIGURE 1 near the ERROR
comment?
-- FIGURE 1:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
int, @.timeID int, @.dtMaxDate datetime
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.timeID = '1'
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
IF @.timeID = 1 -- ERROR IS IN NEXT 2 LINES
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ' +
CAST(@.dtMaxDate AS VARCHAR(55))
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
EXEC(@.SQL)
-- FIGURE 2:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID int
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM
Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55)) + '
GROUP BY
Customers.CompanyName'What's the error you're getting?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"scott" <sbailey@.mileslumber.com> wrote in message
news:OVCoeAt7FHA.204@.TK2MSFTNGP10.phx.gbl...
> In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
> However, in FIGURE 1, I'm trying to add a WHERE subquery that should
> return FIGURE 2's results but only for the last 10 days. I'm getting a
> strange error in QA. Can someone help me with my syntax in FIGURE 1 near
> the ERROR comment?
> -- FIGURE 1:
> declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
> int, @.timeID int, @.dtMaxDate datetime
> SET @.Country = N'Germany'
> SET @.ShipVia = '1'
> set @.typeID = '1'
> SET @.timeID = '1'
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> SET @.SQL = 'SELECT Customers.CompanyName, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + '
> FROM Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
> IF @.timeID = 1 -- ERROR IS IN NEXT 2 LINES
> SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ' +
> CAST(@.dtMaxDate AS VARCHAR(55))
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
> EXEC(@.SQL)
> -- FIGURE 2:
> declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
> int
> SET @.Country = N'Germany'
> SET @.ShipVia = '1'
> set @.typeID = '1'
> SET @.SQL = 'SELECT Customers.CompanyName, '
> IF @.typeID = 1
> SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
> IF @.typeID = 2
> SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
> SET @.SQL = @.SQL + '
> FROM
> Customers
> INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE
> Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55)) + '
> GROUP BY
> Customers.CompanyName'
>|||scott (sbailey@.mileslumber.com) writes:
> In FIGURE 2, I'm successfully building a dynamic statement on Northwind.
> However, in FIGURE 1, I'm trying to add a WHERE subquery that should
> return
> FIGURE 2's results but only for the last 10 days. I'm getting a strange
> error in QA. Can someone help me with my syntax in FIGURE 1 near the ERROR
> comment?
Please, next time include the error message you get.
Here is what I think is suspect:

> CAST(@.dtMaxDate AS VARCHAR(55))
Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
is not subject to different interpretation.
Or even, better throw this code away entirely. I don't know what you
are trying to achieve, but if this is an exercise with dynamic SQL,
please to it the proper way and use sp_executesql instead. Then you
don't have to deal with cast and convert to interpolate variable values.
Please see sp_executesql in Books Online.
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|||Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '6'.
What's confusing is their is no '6' in my code.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uadSxFt7FHA.2176@.TK2MSFTNGP14.phx.gbl...
> What's the error you're getting?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:OVCoeAt7FHA.204@.TK2MSFTNGP10.phx.gbl...
>|||Below in FIGURE 1 is my code using the new date format. I'm getting the
below error below. Can you help me solve this? I hear what you're saying
about the other method, but want to get this one fixed due to time
constraints.
ERROR:
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
-- FIGURE 1:
declare @.SQL varchar(1000), @.ShipVia int, @.Country nvarchar(15), @.typeID
int, @.timeID int, @.dtMaxDate datetime
SET @.Country = N'Germany'
SET @.ShipVia = '1'
set @.typeID = '1'
SET @.timeID = '1'
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
SET @.SQL = 'SELECT Customers.CompanyName, '
IF @.typeID = 1
SET @.SQL = @.SQL + 'SUM(Orders.Freight) AS SumOfFreight '
IF @.typeID = 2
SET @.SQL = @.SQL + 'COUNT(*) AS SalesCount '
SET @.SQL = @.SQL + '
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE
Orders.ShipVia = ' + CAST(@.ShipVia AS VARCHAR(55))
IF @.timeID = 1
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ' +
convert(char(8), @.dtMaxDate, 112)
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Customers.CompanyName'
EXEC(@.SQL)
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715E9F9B8E8FYazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Please, next time include the error message you get.
> Here is what I think is suspect:
>
> Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
> is not subject to different interpretation.
> Or even, better throw this code away entirely. I don't know what you
> are trying to achieve, but if this is an exercise with dynamic SQL,
> please to it the proper way and use sp_executesql instead. Then you
> don't have to deal with cast and convert to interpolate variable values.
> Please see sp_executesql in Books Online.
> --
> 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|||CONVERT (varchar(12), @.dtMaxDate, 101) fixed it. thanks for your input.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9715E9F9B8E8FYazorman@.127.0.0.1...
> scott (sbailey@.mileslumber.com) writes:
> Please, next time include the error message you get.
> Here is what I think is suspect:
>
> Use "convert(char(8), @.dtMaxDate, 112)" to get a date format that
> is not subject to different interpretation.
> Or even, better throw this code away entirely. I don't know what you
> are trying to achieve, but if this is an exercise with dynamic SQL,
> please to it the proper way and use sp_executesql instead. Then you
> don't have to deal with cast and convert to interpolate variable values.
> Please see sp_executesql in Books Online.
> --
> 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 (sbailey@.mileslumber.com) writes:
> Server: Msg 170, Level 15, State 1, Line 7
> Line 7: Incorrect syntax near '6'.
> What's confusing is their is no '6' in my code.
It's imperative when you work with dynamic SQL to include things like:
IF @.debug = 1 PRINT @.sql
so that you can see what you actually have produced.
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|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns971668A0F963EYazorman@.127.0.0.1...
> It's imperative when you work with dynamic SQL to include things like:
> IF @.debug = 1 PRINT @.sql
How about, instead, using a macro and putting it in debug mode? :)
http://www.datamanipulation.net/tsqlmacro
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

> so that you can see what you actually have produced.
> --
> 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|||On Mon, 21 Nov 2005 18:19:56 -0600, Scott wrote:

>CONVERT (varchar(12), @.dtMaxDate, 101) fixed it. thanks for your input.
Hi Scott,
I severely doubt it. You're not getting errors anymore, but your results
are incorrect.
If you had followed Erlands original advice to print the SQL string you
are executing, you'd have seen that your original query created code
like this:
WHERE Orders.OrderDate <= 20051123
Since 20051123 is a numeric and OrderDate is a datetime, SQL Server
attempts to convertthe numeric 20051123 to datetime. Conversion rule
says that the number is the number of days since jan 1st 1900. If I
didn't miscalculate, adding 20,051,123 to the start of 1900 takes us
some 55,000 years in the future - well beyond the maximum capacity of
the datetime datatype. That's whhy you got the error.
Your "fix" now generates code like this:
WHERE Orders.OrderDate <= 11/23/2005
This will first divide 11 by 23 (using integer division, yielding 0),
then divide that by 2005 (result still 0). This will then be converted
to datetime ==> 0 days after jan 1st 1900 is jan 1st 1900. Regardless of
the value of @.dtMaxDate, you'll be comparing to 1900-01-01.
The correct way to pass a date literal is to use the yyyymmdd format,
and to enclose it in single quotes:
WHERE Orders.OrderDate <= '20051123'
Or to fix this particular line of your code:
SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' +
convert(char(8), @.dtMaxDate, 112) + ''''
(Note that a single quotes within single quotes must be doubled!)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks, that fixed it.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:4vd7o19vos75ef7eumvphndgcafspqkjv7@.
4ax.com...
> On Mon, 21 Nov 2005 18:19:56 -0600, Scott wrote:
>
> Hi Scott,
> I severely doubt it. You're not getting errors anymore, but your results
> are incorrect.
> If you had followed Erlands original advice to print the SQL string you
> are executing, you'd have seen that your original query created code
> like this:
> WHERE Orders.OrderDate <= 20051123
> Since 20051123 is a numeric and OrderDate is a datetime, SQL Server
> attempts to convertthe numeric 20051123 to datetime. Conversion rule
> says that the number is the number of days since jan 1st 1900. If I
> didn't miscalculate, adding 20,051,123 to the start of 1900 takes us
> some 55,000 years in the future - well beyond the maximum capacity of
> the datetime datatype. That's whhy you got the error.
>
> Your "fix" now generates code like this:
> WHERE Orders.OrderDate <= 11/23/2005
> This will first divide 11 by 23 (using integer division, yielding 0),
> then divide that by 2005 (result still 0). This will then be converted
> to datetime ==> 0 days after jan 1st 1900 is jan 1st 1900. Regardless of
> the value of @.dtMaxDate, you'll be comparing to 1900-01-01.
>
> The correct way to pass a date literal is to use the yyyymmdd format,
> and to enclose it in single quotes:
> WHERE Orders.OrderDate <= '20051123'
> Or to fix this particular line of your code:
> SET @.SQL = @.SQL + ' AND Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' +
> convert(char(8), @.dtMaxDate, 112) + ''''
> (Note that a single quotes within single quotes must be doubled!)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)