Re: Using variables in Dynamic SQL

From: Roji. P. Thomas (lazydragon_at_nowhere.com)
Date: 08/31/04 

Date: Tue, 31 Aug 2004 19:31:37 +0530

http://www.sommarskog.se/dynamic_sql.html 

-- 
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"rzirpolo" <robert.zirpolo@phoenixdb.co.uk> wrote in message
news:5320d330.0408310536.6fdeac58@posting.google.com...
> 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."
>
> 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 =
> '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 >= '''+ @startdate +''')
> and (b.departureDateTime < '''+ dateadd(day,1,@endDate) +''')'
>
> exec(@sql)
>
> I realise what is causing this are the lines,
>
> and (b.departureDateTime >= '''+ @startdate +''')
> and (b.departureDateTime < '''+ dateadd(day,1,@endDate) +''')
>
> 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.
>
> 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.

Relevant Pages

  • Re: return a value using MDX
    ... I created a stored procedure to this query. ... it is looking for @sql as well. ... DECLARE @sql varchar ...
    (microsoft.public.sqlserver.olap)
  • Re: return a value using MDX
    ... I created a stored procedure to this query. ... it is looking for @sql as well. ... DECLARE @sql varchar ...
    (microsoft.public.sqlserver.olap)
  • XML with cursor - why is it not moving to the next node?
    ... I want the query to loop through the 'sql' nodes and execute each query. ... DECLARE @strXMLData varchar ... DECLARE @strSQL varchar ...
    (microsoft.public.sqlserver.xml)
  • Re: return a value using MDX
    ... There are some good books like Fast track MDX which I have ... I created a stored procedure to this query. ... it is looking for @sql as well. ... DECLARE @sql varchar ...
    (microsoft.public.sqlserver.olap)
  • Re: Using sp_executesql to dynamically query xml
    ... requires the use of dynamic SQL because I do not know how the xml will be ... XQuery code doesn't look correct either. ... To start, you should probably build a single string for your query, and not use ... DECLARE @SqlCommand nvarchar ...
    (microsoft.public.sqlserver.xml)
Loading