Re: Problem with dynamic SQL syntax

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Steen Persson (SPE_at_REMOVEdatea.dk)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 15:29:09 +0200

Hi Araron

I think the ' was a remicense for my playing around with the statement, so
that's not the cause of the problem- sorry for the confusion.

I do know the difference between an empty field and NULL (or at least I hope
I know...:-)...) so I'm sorry if I've made some confusions about this. The
statement actually works when I'm running it without the variables and
without the dynamic SQL. It's not until I add the variable and dyn. SQL
part, I have problems getting it to accept the ' ' in the end. I'm not a all
experienced in using dynamic SQL so I thought that it was just something
simple I was missing in the syntax.
I'll take a closer look at your suggestion to see if that will do the trick.

Thanks

Steen

"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
>
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
>
> DECLARE @sql NVARCHAR(2000)
> SET @sql = N'SELECT <column_list> INTO
'+@db_name_dest+'.dbo.'+@table_name+'
> FROM '+@servername_source+'.'+@db_name_source+'.dbo.'+@table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != '''''
>
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>
> "Steen Persson" <SPE@REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @sql = 'SELECT * INTO '+@db_name_dest+'.dbo.'+@table_name+'
> > FROM '+@servername_source+'.'+@db_name_source+'.dbo.'+@table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @sql....." statement?
> >
> > Best Regards
> > Steen
> >
> >
>
>

Relevant Pages

  • Re: Problem with dynamic SQL syntax
    ... I think you're having a comprehension problem between an empty/blank string ... Anyway, your syntax is wrong. ... > I' m having a problem with the syntax when I'm trying to run a dynamic SQL ...
    (microsoft.public.sqlserver.server)
  • Re: Problem with dynamic SQL syntax
    ... I've now tried to play around with both my own code and the suggestion from ... When I then try to use the variables and run it as dynamic SQL, ... with the syntax error around the '' in the end. ... > string, so of course the EXEC call will break. ...
    (microsoft.public.sqlserver.server)
  • Re: Query question
    ... > dynamic sql inside of a stored procedure. ... > that allow any string as a substitute. ... The query execution plan can be reused. ...
    (microsoft.public.sqlserver.server)
  • Re: PROBLEM SOLVED> I have an SP that accepts a WHERE clause as a parameter. Doesnt work ...kin
    ... "where" condition as it was passed into the SP, in the dynamic sql string. ... > "Larry Woods" wrote in message ... >> canceldate and receiveddate are Date fields; ...
    (microsoft.public.sqlserver.programming)
  • Re: IDS 11.5
    ... Oh, if you are using any IDS release prior to 11.50, Dynamic SQL is not ... supported except using the Exec Datablade which uses different syntax. ... IIUG Board of Directors ... Please keep in mind that my own opinions are my own opinions ...
    (comp.databases.informix)