Re: Multiple variables

Thanks Hugo

This has worked perfectly - its going to save me an awful lot of time!!!

Andy

"Hugo Kornelis" wrote:

> On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
>
> (snip)
> >As you can see it is because the quote before C is closing the string and so
> >on. Do I need to break the string at that point and concatenate it so that:
> >
> >pay_or_contact = ' + 'C' + ', etc etc
> >
> >or is there a way of getting SQL to ignore the string within the string?
> (snip)
>
> Hi Andy,
>
> You'll have to make sure that you embed a quote within the string. The
> code passed to the EXEC (@sql) needs quotes around the C to recognise it
> as a literal. There are two ways to do that: either use the ASCII value
> for the quote, or double the quote (one quote in a string is considered
> a string delimiter, two consecutive quotes are considered one quote as
> part of the string):
>
> --Update the various flags on the company basic table
> SET @sql = 'UPDATE company_basic
> SET report_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ''C'',
> year_end_date = null,
> --Other fields being set to Null
> WHERE company_code IN ' + @codes
> EXEC (@sql)
>
> or
>
> --Update the various flags on the company basic table
> SET @sql = 'UPDATE company_basic
> SET report_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
> year_end_date = null,
> --Other fields being set to Null
> WHERE company_code IN ' + @codes
> EXEC (@sql)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
.

Relevant Pages

  • Re: Syntax
    ... tablename above except that it is an mdb rather than a table in SQL server. ... Thus my connection table is now the mdb file name minus the ".mdb" extension ... the Access mdb connection string should be the variable name as ...
    (microsoft.public.sqlserver.dts)
  • Re: Difference between and : symbols
    ... lisp> ... You can inhibit evaluation with the special operator QUOTE. ... The specific thing that confused you is why ASDF functions ... But what ASDF really wants is a string. ...
    (comp.lang.lisp)
  • Re: Identical SPROCS one with an error the other OK???
    ... You are using double quotes within the building of the string in the first ... you need only escape them with an additional quote. ... I have a SPROC that runs perfectly from SQL Query ...
    (microsoft.public.sqlserver.programming)
  • Re: fastest way to change case of string
    ... string rather than concatenating a new string is indeed much faster. ... Dim quoted As Collection ... Const QUOTE = "'" ... ChangeCaseX = StrConv ...
    (microsoft.public.vb.general.discussion)
  • MD5 Hash with single quote = grief in dao.findfirst
    ... I know when you need to create a query string and the data contains a single ... quote, you must double the quote as an escape sequence. ... Hundreds of assumption cells combined into one 16 character Hash. ... I build the criteria SQL string. ...
    (microsoft.public.access.modulesdaovba)