Re: Multiple variables

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
>
>DELETE company_size
>WHERE company_code = @code
>
>--Other deletions go in here
>
>UPDATE company_basic
>SET report_status = null,
> report_entry_urn = null,
> next_rpt_archive = null,
>/* Other fields to be updated... */
>WHERE company_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 = 'DELETE company_size
WHERE company_code IN ' + @code
EXEC (@sql)

--Other deletions go in here

SET @sql = 'UPDATE company_basic
SET report_status = null,
report_entry_urn = null,
next_rpt_archive = null,

/* Other fields to be updated... */
WHERE company_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)
.