Re: Using variables in Dynamic SQL

From: Robert Zirpolo (robert.zirpolo_at_phoenixdb.co.uk)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 07:22:23 -0700

Thank you for the posts and I have now managed to resolve the other
problems with the syntax and am concentrating on the one line with the
dateadd function. My revised syntax is,

DECLARE @sql varchar(5000)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @Media nvarchar(500)
DECLARE @Analysis nvarchar(500)

SELECT @StartDate = '01/07/00'
SELECT @EndDate = '31/12/04'
SELECT @Media = '1, 2, 3'
SELECT @Analysis = '%'

set @sql =
''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 (se.departureDateTime >= '''+convert(char(10),@startdate,103)+''')
and (se.departureDateTime < '''+dateadd(day,1,@enddate)+''''

exec(@sql)

As I said above the problem line is,

and (se.departureDateTime < '''+dateadd(day,1,@enddate)+''''

As that produces the error message "Syntax error converting datetime
from character string."

I am reading through http://www.sommarskog.se/dynamic_sql.html to see if
this can help me out.

If anybody else knows how to resolve this or a workaround then please
post a reply.

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Using variables in Dynamic SQL
    ... > Thank you for the posts and I have now managed to resolve the other ... My revised syntax is, ... > DECLARE @StartDate datetime ... > If anybody else knows how to resolve this or a workaround then please ...
    (microsoft.public.sqlserver.programming)
  • Re: Need help on modifying and assembly of a small program!
    ... |> downloaded your assembler so that's handy...I can just strip the rest out ... | I really hope that this doesn't mean, that the 68k syntax is so ... I think all Asm-coders are familiar with 'the agreed standard' as used ... Perhaps you try a standard disassembler for code posts? ...
    (alt.lang.asm)
  • Re: homogenous syntax for type declarations
    ... > syntax to declare a typed variable is different each time: ... Another difference with specialized lambda lists in DEFMETHOD is ...
    (comp.lang.lisp)
  • Re: Access 3122 Error in Update query
    ... SQL is anything BUT universal. ... differences in syntax amongst all the major databases...so don't just blame Access. ... MS Access doesn't support aggregate Updates? ...
    (microsoft.public.access.adp.sqlserver)
  • best blog for PHP project
    ... you guys (and gals) used that you like? ... Wiki like syntax preferred over HTML like syntax for story editor. ... Categorize posts ... Advertising-support is not needed (some seem pretty advertising ...
    (php.general)