Hi
Our database has a series of tables that are all linked by a field called
company_code (amongst others). Occasionally data needs to be deleted and
this involves deleting multiple rows from some of these tables and updating
data in another table. I tend to wait until there are a few to do and then
do them all at once. To make the task less onerous I have saved a simple
script that does all the deletions for each code at once, a cut down version
is below:
/*Delete all pay data and make contact only*/
DECLARE @.code int
SET @.code = 12345
DELETEcompany_size
WHEREcompany_code = @.code
--Other deletions go in here
UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code = @.code
What I would like to be able to do is change the 'where' statements so that
they use the IN operator rather than the = operator. Is there a way to do
this using variables so I can assign a list of company codes to a variable
that can be used with an IN operator?
Thanks
Andy
On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:
>Hi
>Our database has a series of tables that are all linked by a field called
>company_code (amongst others). Occasionally data needs to be deleted and
>this involves deleting multiple rows from some of these tables and updating
>data in another table. I tend to wait until there are a few to do and then
>do them all at once. To make the task less onerous I have saved a simple
>script that does all the deletions for each code at once, a cut down version
>is below:
>/*Delete all pay data and make contact only*/
>DECLARE @.code int
>SET @.code = 12345
>DELETEcompany_size
>WHEREcompany_code = @.code
>--Other deletions go in here
>UPDATEcompany_basic
>SETreport_status = null,
>report_entry_urn = null,
>next_rpt_archive = null,
>/* Other fields to be updated... */
>WHEREcompany_code = @.code
>What I would like to be able to do is change the 'where' statements so that
>they use the IN operator rather than the = operator. Is there a way to do
>this using variables so I can assign a list of company codes to a variable
>that can be used with an IN operator?
>Thanks
>Andy
>
Hi Andy,
To do that, you'll have to use dynamic SQL. Since this is in a script
that only you can execute, you don't have to worry about SQL Injection
in this case.
Rough outline:
/*Delete all pay data and make contact only*/
DECLARE @.codes nvarchar(40)
DECLARE @.SQL nvarchar(4000)
SET @.code = N'(12345,6789)'
SET @.sql = 'DELETEcompany_size
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
--Other deletions go in here
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
In case you're tempted to use this technique in your production code as
well, read up on SQL injection and other dangers of this technique:
http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||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.
Is the N in this line correct: SET @.code = N'(12345,6789)'?
I'm not brave enough to give it a go anyway!!!
Thanks again
"Hugo Kornelis" wrote:
> On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:
>
> Hi Andy,
> To do that, you'll have to use dynamic SQL. Since this is in a script
> that only you can execute, you don't have to worry about SQL Injection
> in this case.
> Rough outline:
> /*Delete all pay data and make contact only*/
> DECLARE @.codes nvarchar(40)
> DECLARE @.SQL nvarchar(4000)
> SET @.code = N'(12345,6789)'
> SET @.sql = 'DELETEcompany_size
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
> --Other deletions go in here
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> report_entry_urn = null,
> next_rpt_archive = null,
> /* Other fields to be updated... */
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
>
> In case you're tempted to use this technique in your production code as
> well, read up on SQL injection and other dangers of this technique:
> http://www.sommarskog.se/dynamic_sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||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)
|||Hi Hugo
Thanks for this. I've been out of the office the last few days so have only
just picked this up. Thanks for the advice as well!
Andy
"Hugo Kornelis" wrote:
> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> 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>
>
> 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).
>
> 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)
>
|||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 = 'UPDATEcompany_basic
SETreport_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
WHEREcompany_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 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>
>
> 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).
>
> 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)
>
|||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 = 'UPDATEcompany_basic
SETreport_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
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
or
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_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
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||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)
> (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 = 'UPDATEcompany_basic
> SETreport_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
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> or
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_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
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
No comments:
Post a Comment