Transact SQL probklems with variable scope

[B Moor]

I have 24 tables named tblData1 ... tblData24 and I have a scheduled
job that runs successfully to delete all data older than 31 days.

My problem is that I need to keep at least one record in each table
for the aggregate function max() to work in one of my application's
functions, as if there are no records the result is null.
Although I have figured out a workaround in the function using max() I
would like to know how to change my script.

Functionally I would like to get the max() value of the ID column
(autoincrementing) and then add to the where "And ID <> @maxID".
I have tried a few options and come unstuck with scope of variables,
and tried to use a temp table to store the max values for the 24
tables and got no where. Can anyone help ?
Working script without the @maxID bit:-
DECLARE @days VARCHAR(12)
DECLARE @intData int
DECLARE @SQL1 VARCHAR(2000)
set @Days = 31
set @intData = 1

While @intData<=24
Begin
SET @SQL1 = 'DELETE FROM [DB1_SQL].[dbo].[tblData'+
rtrim(CONVERT(char(2), @intData)) + '] Where
datediff(Day,Datim,getdate())> '+ @days

EXEC(@SQL1)

/*print @SQL1*/

set @intData= @intData + 1

End

go

[Ross Presser]

On 1 Oct 2004 00:26:09 -0700, B Moor wrote:

I have 24 tables named tblData1 ... tblData24 and I have a scheduled
job that runs successfully to delete all data older than 31 days.

My problem is that I need to keep at least one record in each table
for the aggregate function max() to work in one of my application's
functions, as if there are no records the result is null.
Although I have figured out a workaround in the function using max() I
would like to know how to change my script.

Functionally I would like to get the max() value of the ID column
(autoincrementing) and then add to the where "And ID <> @maxID".
I have tried a few options and come unstuck with scope of variables,
and tried to use a temp table to store the max values for the 24
tables and got no where. Can anyone help ?
You don't need @maxID as a variable; you can use a subselect. See what I
added below:

Working script without the @maxID bit:-
DECLARE @days VARCHAR(12)
DECLARE @intData int
DECLARE @SQL1 VARCHAR(2000)
set @Days = 31
set @intData = 1

While @intData<=24
Begin
SET @SQL1 = 'DELETE FROM [DB1_SQL].[dbo].[tblData'+
rtrim(CONVERT(char(2), @intData)) + '] Where
datediff(Day,Datim,getdate())> '+ @days + ' AND ID <> ( SELECT MAX(ID) FROM [DB1_SQL].[dbo].[tblData'+
rtrim(CONVERT(char(2), @intDat)) + '] )'
EXEC(@SQL1)

/*print @SQL1*/

set @intData= @intData + 1

End

go

[B Moor]

thank you , worked a treat