Can seem to get delete and exist to work right

Hi all,

I am writing a test result database in SQL 2K5 and one of the features I want to implement is a stored procedure that deletes the oldrecords while preserving a set number of records, the following is what my SP looks like

Procedure [dbo].[CleanResults]

@.RecsToKeep bigInt,

@.Output Int output

as

Declare @.Date Datetime

Declare @.Count bigint

set @.Date = getdate()

Select @.Count = Count(UniqueID) from [Main]

Print 'THE COUNT IS'

print @.Count

if ( @.Count > @.RecsToKeep) begin

set @.Count= @.Count - @.RecsToKeep

Print 'THE Number to delete is'

print @.Count

select TOP(@.Count) UniqueID from Main order By [Main].[TestDateTime] desc

Delete from [Main] where exists (select TOP(@.Count) * from Main order By [Main].[TestDateTime] desc);

set @.Output = @.Count

end

else begin

set @.Output = -1

end

whats odd is that the select staement will evaluate correctly and return the oldest record @.Count record, however the delete stament removes all the records. An advice would be appriciated.

Thanks Christopher

PS Any advice for using TOP with variables in MSDE 2k (as opposed to 2k5) would be appreciated

The WHERE EXISTS is not what you are wanting. Try this instead

Code Snippet

Delete from [Main] where UniqueID IN (select TOP(@.Count) UniqueID from Main order By [Main].[TestDateTime] desc);

This will just grab the first set of uniqueIDs and delete those records, which will be the correct number of records.|||

Thanks it worked

|||

You can try:

;with cte

as

(

select *, row_number() over(order by TestDateTime DESC) as rn

from Main

)

delete cte

where rn <= @.Count;

AMB