My dynamic sql correctly returns the top 10 dates with records if @.timeID
equals 1. I need the @.timeID equals 2 part to return records between
@.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
keep my example simple).
In the below section where @.timeID = 2, I'm getting a syntax error. Can
anyone help with the part below that tests IF @.timeID = 2 ? I just need some
quote help with that part of the where clause.
This runs on Northwind.
CODE ****************************************
***
declare @.SQL varchar(1000), @.typeID int, @.timeID int
declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
set @.typeID = 1
SET @.timeID = 1
set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
set @.dtEndDate = @.dtMaxDate
SET @.SQL = 'SELECT Orders.OrderDate, '
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 '
IF @.timeID = 1 /* Return last 10 days */
SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
Orders.OrderDate
FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
@.dtMaxDate, 112) + ''''
IF @.timeID = 2 /* should Return records between start and end date */
PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
@.dtStartDate + ' AND '
+ CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
EXEC(@.SQL)Basically, I think your dynamic SQL should look like this when done:
=====
'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
@.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
CONVERT(CHAR(11), @.dtEndDate) + ''''
=====
From your example below, it looks like the CONVERT portion needs to be
within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
to the query with their values. Also, what date format are you expecting by
converting into CHAR(8)?
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> 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 '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott
I don't understand why would want to use dynamic sql but this is a fixed
code
-- PRINT 'BELOW CODE GIVES ERROR'
SET @.SQL = @.SQL + 'CONVERT (char(8), Orders.OrderDate, 112) >=''' +
convert(char(8),@.dtStartDate ,112)+ ''' AND '
+ 'CONVERT (char(8), Orders.OrderDate, 112) <='''
+convert(char(8),@.dtEndDate,112)+''''
BTW if IF @.timeID = 1, I got this error
Line 28: Incorrect syntax near '>'.
"Scott" <sbailey@.mileslumber.com> wrote in message
news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
> In the below section where @.timeID = 2, I'm getting a syntax error. Can
> anyone help with the part below that tests IF @.timeID = 2 ? I just need
> some quote help with that part of the where clause.
> This runs on Northwind.
> CODE ****************************************
***
> declare @.SQL varchar(1000), @.typeID int, @.timeID int
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.dtMaxDate datetime
> set @.typeID = 1
> SET @.timeID = 1
> set @.dtMaxDate = (SELECT MAX(OrderDate) FROM Orders)
> set @.dtStartDate = (SELECT DATEADD(day, -20, @.dtMaxDate))
> set @.dtEndDate = @.dtMaxDate
> SET @.SQL = 'SELECT Orders.OrderDate, '
> 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 '
> IF @.timeID = 1 /* Return last 10 days */
> SET @.SQL = @.SQL + ' Orders.OrderDate IN (SELECT DISTINCT TOP 10
> Orders.OrderDate
> FROM Orders WHERE Orders.OrderDate <= ''' + convert(char(8),
> @.dtMaxDate, 112) + ''''
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
> + CONVERT (char(8), Orders.OrderDate, 112) <= @.dtEndDate
> SET @.SQL = @.SQL + ' ORDER BY Orders.OrderDate DESC) '
> SET @.SQL = @.SQL + 'GROUP BY Orders.OrderDate'
> EXEC(@.SQL)
>
>|||Scott (sbailey@.mileslumber.com) writes:
> My dynamic sql correctly returns the top 10 dates with records if @.timeID
> equals 1. I need the @.timeID equals 2 part to return records between
> @.dtStartDate and @.dtEndDate. (I just set the enddate equal to maxdate to
> keep my example simple).
Weren't you the guy with time constraints? Why then are you wasting the
time with building complete SQL strings, when using sp_executesql is so
much easier?
And why do you insist on not printing out the generated SQL? You could
at least include the generated SQL in your posts, so that it's easier for
people to spot the syntax errors.
> IF @.timeID = 2 /* should Return records between start and end date */
> PRINT 'BELOW CODE GIVES ERROR'
> SET @.SQL = @.SQL + CONVERT (char(8), Orders.OrderDate, 112) >=
> @.dtStartDate + ' AND '
You have string concatanated with string, and then there is a comparison
operator in the middle of all this. That will obviously give a syntax
error.
And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
an WHERE clause. When you put a column in an expression, this prevents
any index from that column from being used. Thus, this can have a serious
impact on performance.
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|||1. I'd really appreciate it if you would expand on your "avoid doing things
like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to pass dates
from ASP to my SPROCs as "11/24/2005". How or what syntax can I use on that
date format to avoid a performance hit?
2. As far as sp_executesql, I wanted to use that method, but the syntax is
more confusing that my method. If I good get some good simple examples, I'd
go that way.
3. My reason for going the way I am is if I hard coded every sql statement
with the matrix of variables I'm sending this SPROC, it would be 1,000 lines
or more. Normally they don't get this out of hand, but this one is for a
graph with lots of options.
4. I'll do better at printing the SQL from now on.
Thanks for any pointers on my date performance issue #1 above.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718610C29F6DYazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> Weren't you the guy with time constraints? Why then are you wasting the
> time with building complete SQL strings, when using sp_executesql is so
> much easier?
> And why do you insist on not printing out the generated SQL? You could
> at least include the generated SQL in your posts, so that it's easier for
> people to spot the syntax errors.
>
> You have string concatanated with string, and then there is a comparison
> operator in the middle of all this. That will obviously give a syntax
> error.
> And avoid doing things like CONVERT (char(8), Orders.OrderDate, 112) in
> an WHERE clause. When you put a column in an expression, this prevents
> any index from that column from being used. Thus, this can have a serious
> impact on performance.
> --
> 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|||Thanks for the efforts.
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:enDhqcL8FHA.1140@.tk2msftngp13.phx.gbl...
> Basically, I think your dynamic SQL should look like this when done:
> =====
> 'SELECT Orders.OrderDate, SUM(Orders.Freight) AS SumOfFreight FROM
> Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
> WHERE CONVERT (char(8), Orders.OrderDate, 112) >= ''' + CONVERT (CHAR(11),
> @.dtStartDate) + ''' AND CONVERT (char(8), Orders.OrderDate, 112) <= ''' +
> CONVERT(CHAR(11), @.dtEndDate) + ''''
> =====
> From your example below, it looks like the CONVERT portion needs to be
> within the quotes and the @.tdStartDate and @.tdEndDates need to be appended
> to the query with their values. Also, what date format are you expecting
> by converting into CHAR(8)?
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott" <sbailey@.mileslumber.com> wrote in message
> news:e3P2oYJ8FHA.2192@.TK2MSFTNGP14.phx.gbl...
>|||Scott (sbailey@.mileslumber.com) writes:
> 1. I'd really appreciate it if you would expand on your "avoid doing
> things like CONVERT (char(8)), Orders.OrderDate, 112)". I always have to
> pass dates from ASP to my SPROCs as "11/24/2005". How or what syntax can
> I use on that date format to avoid a performance hit?
No, you don't have that pass dates that way from ASP. Even more you
shouldn't. That is not even a date as far as I am concerned.
The way to pass data from a client is to use parameters, and let the
the client interpret the date according to the regional setting. The is
then passed to SQL Server as a binary value, as dates are binary values
in SQL Server. (As they are in Windows, by the way.)
I don't do ASP, but in plain Visual Basic it looks something like:
cmd.CommandType = adCmdStoredProcedure
cmd.CommandText = "dbo.some_sp"
cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
And obviously you are doing something like that already, as the date
variables in your example were declared as datetime, not as character.
> 2. As far as sp_executesql, I wanted to use that method, but the syntax
> is more confusing that my method. If I good get some good simple
> examples, I'd go that way.
SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
@.params = N'@.par1 int, @.par2 datetime'
EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
How is this more confusing that trying to get quotes and date formats
correct, something you appear to have big problems with.
As for writing dynamic searches, have you looked at my web site where
I have a longer article on the topic, with both static and dynamic methods?
http://www.sommarskog.se/dyn-search.html. There are also tips for people
that use EXEC().
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|||The part of sp_executesql that gets confusing is the N' syntax. Does N' mean
it's a text type?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> 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 your site in search_orders_1, for example, how does SQL know what
@.xorderid is when you only declare @.orderid at the beginning of the SPROC? I
see @.xorderid listed in the @.paramlist after you use it as a WHERE criteria,
but how does SQL know what @.xorderid is?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9718E396131E5Yazorman@.127.0.0.1...
> Scott (sbailey@.mileslumber.com) writes:
> No, you don't have that pass dates that way from ASP. Even more you
> shouldn't. That is not even a date as far as I am concerned.
> The way to pass data from a client is to use parameters, and let the
> the client interpret the date according to the regional setting. The is
> then passed to SQL Server as a binary value, as dates are binary values
> in SQL Server. (As they are in Windows, by the way.)
> I don't do ASP, but in plain Visual Basic it looks something like:
> cmd.CommandType = adCmdStoredProcedure
> cmd.CommandText = "dbo.some_sp"
> cmd.CreateParameter "@.param1", adDBTimeStamp, adParamInput, , DateFld
> And obviously you are doing something like that already, as the date
> variables in your example were declared as datetime, not as character.
>
> SELECT @.sql = N'SELECT ... FROM tbl WHERE col = @.par1, col = @.par2',
> @.params = N'@.par1 int, @.par2 datetime'
> EXEC sp_executesql @.sql, @.params, @.par1 = 12, @.par2 = @.dtEndTime
> How is this more confusing that trying to get quotes and date formats
> correct, something you appear to have big problems with.
> As for writing dynamic searches, have you looked at my web site where
> I have a longer article on the topic, with both static and dynamic
> methods?
> http://www.sommarskog.se/dyn-search.html. There are also tips for people
> that use EXEC().
>
> --
> 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|||> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
It mean it is Unicode.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Scott" <sbailey@.mileslumber.com> wrote in message news:ut24UCY8FHA.3952@.TK2MSFTNGP12.phx.g
bl...
> The part of sp_executesql that gets confusing is the N' syntax. Does N' me
an it's a text type?
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9718E396131E5Yazorman@.127.0.0.1...
>
Benners_J: