Re: Multiple variables

Hi Hugo
I'm getting a syntax error when running this script because oneof the fields
that gets updated is a char data type so:

--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)

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?

Sorry I didn't include that bit in the initial question - I was trying to
cut down on the size of the post!

Thanks
Andy

"Hugo Kornelis" wrote:

> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> >Hi Hugo
> >
> >Thanks very much for this - I've not tried it yet as I think there might be
> >a couple of typos that I want to confirm:
> >
> >You declare a variable called @codes, presumably all of my old instances of
> >@code should now read @codes instead.
>
> Hi Andy,
>
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @code to
> @codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
>
> >Is the N in this line correct: SET @code = N'(12345,6789)'?
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
>
> >I'm not brave enough to give it a go anyway!!!
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
>
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
.

Relevant Pages