Re: Using variables in Dynamic SQL
From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/31/04
- Next message: Rico: "SQL statement question"
- Previous message: Adam Machanic: "Re: Do I need to use a cursor?"
- In reply to: rzirpolo: "Using variables in Dynamic SQL"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 31 Aug 2004 16:56:57 +0200
On 31 Aug 2004 06:36:05 -0700, rzirpolo wrote:
>When attempting to run the following query to generate and then
>execute dynamic SQL I am getting the error message "Syntax error
>converting datetime from character string."
>
(snip code)
>I realise what is causing this are the lines,
>
> and (b.departureDateTime >= '''+ @startdate +''')
> and (b.departureDateTime < '''+ dateadd(day,1,@endDate) +''')
Wrong. This is caused by "SELECT @EndDate = '31/12/04'". I don't know why
you insist on using this format when I've already told you twice that it
doesn't work.
Another thing that has already been told you is that you also need to use
the CONVERT function in this piece of the code to format the date back to
YYYYMMDD format. I honestly don't know why you ask here for advise, then
choose to ignore the advise you get and simply repost the problem.
>Can anybody tell me what the correct format should be ?
>
>But if I simply remove these clauses and run the statement I get the
>error "Line 2: Incorrect syntax near '31/12/2004'."
>
>Which means it doesn't like the format of the select statement,
>
>select
> ''Departing from ''+ '''+convert(char(10),@startdate,103)+''' '' to
>'' '''+convert(char(10),@enddate,103)+''' as ''ReportString''
>
>So my 2nd query is can anybody advise me of the correct format here.
If you print the resulting string in @sql, you'll see that you have some
unmatched single quotes and some missing + signs. See below for a greatly
simplified version that won't cause errors.
The dates don't cause any problem here - if you have corrected the initial
assignment to @EndDate. If that assignment is wrong, it won't be executed,
@EndDate will remain NULL and will cause extra errors in this line.
>I need to use dynamic SQL for this so I am only looking for a
>assistance in regards to amending the text populated in the @sql
>variable.
I'm not so sure that you really need dynamic SQL. Have a look at Erland's
site that gives some alternatives AND warns you about the danger of SQL
injection attacks. http://www.sommarskog.se/dynamic_sql.html
That being said - the following seems to work (though I don't have a
bookings table on my system to test it - but it does pass the syntax
check).
DECLARE @sql varchar(5000)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @Media nvarchar(500)
DECLARE @Analysis nvarchar(500)
SELECT @StartDate = '20000701' --'01/07/00'
SELECT @EndDate = '20041231' --'31/12/04'
SELECT @Media = '1, 2, 3'
SELECT @Analysis = '%'
set @sql =
'select
''Departing from '+convert(char(10),@StartDate,103)+' to
'+convert(char(10),@EndDate,103)+''' as ''ReportString''
from booking as b
where (b.disabled = 0)
and (b.leadsector = 1)
and (b.mediaid in ('+ @Media +'))
and (b.analysiscodeid like '''+ @Analysis +''')
and (b.departureDateTime >= '''+ convert(char(8),@StartDate,112)
+''')
and (b.departureDateTime < '''+
convert(char(8),dateadd(day,1,@EndDate),112) +''')'
print(@sql)
exec(@sql)
Best, Hugo
-- (Remove _NO_ and _SPAM_ to get my e-mail address)
- Next message: Rico: "SQL statement question"
- Previous message: Adam Machanic: "Re: Do I need to use a cursor?"
- In reply to: rzirpolo: "Using variables in Dynamic SQL"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
