Re: Using variables in Dynamic SQL

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

Date: Tue, 31 Aug 2004 08:02:54 -0700

The workaround I have used is that I have setup a second variable which
performs the dateadd function so I can simply reference this in the
where clause. The finished script...

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

SELECT @StartDate = '01/07/00'
SELECT @EndDate = '31/12/04'
SELECT @EndDate2 = dateadd(day,1,@Enddate)
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 < '''+convert(char(10),@enddate2,103)+''')'

exec(@sql)

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

Relevant Pages

  • Re: Dynamic SQL: Syntax error converting datetime from character string
    ... Here is the full syntax of the original statement. ... DECLARE @StartDate datetime ... DECLARE @EndDate datetime ...
    (microsoft.public.sqlserver.programming)
  • Re: Using variables in Dynamic SQL
    ... performs the dateadd function so I can simply reference this in the ... DECLARE @StartDate datetime ... DECLARE @EndDate datetime ...
    (microsoft.public.sqlserver.programming)
  • Re: Execute SQL procedure with parameters
    ... DECLARE @BeginDate datetime ... DECLARE @EndDate datetime ... EXEC CTL_RevenuebyLead_proc ?,? ...
    (microsoft.public.excel.programming)
  • Re: New to Perl: Need help with a script
    ... It might take some getting used to (you'll need to declare your variables, ... >sub usage() { ... You might want to use a here-doc (perldoc perldata) instead, ... pointing to the same array reference. ...
    (comp.lang.perl.misc)
  • Re: Excel Automation
    ... Declare a conditional compilation constant at the start of your module: ... #If EarlyBinding Then ... Dim xlApp As Excel.Application ... When you are ready to distribute the app, remove the reference, change the ...
    (microsoft.public.access.formscoding)
Loading