Re: Multiple variables

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

Relevant Pages

  • Re: Query to look up a number of zip codes or area codes for bulk
    ... I am not very familiar with code and SQL. ... >> trying to figure out how to make a query where a user can input several zip ... >> codes at the same time to pull up a list for a bulk mailing. ... > TextBox - just copy the comma-delimited string from the TextBox and.... ...
    (microsoft.public.access.queries)
  • IIS 6 and SQL 2003 sp4
    ... databases in SQL, one called Employees and the other Postal Codes (a.k.a. ... what is the best way to configure a database to be ...
    (microsoft.public.sqlserver.setup)
  • Re: XML vs SQL CE
    ... I have a 42,508 row table in SQL CE that holds Zip Codes, City, State used ... > with the relative speed of searches on an indexed field? ...
    (microsoft.public.pocketpc.developer)
  • Re: Random # of records based on user input
    ... Try the SQL I gave you. ... different set of 50 "random" codes. ... each time you reopen the recordset. ... "Running Microsoft Access 2000" ...
    (microsoft.public.access.queries)
  • Re: How to handle WM_SIZE message in Doc/View architecture???
    ... I declare below member varible to CView: ... and one function member: EnumSystemDevice(). ...
    (microsoft.public.win32.programmer.directx.video)