SPROC - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SPROC

Author

Topic

ischenk
Starting Member

15 Posts

Posted - 2008-07-22 : 22:04:33

Just wanted to know if this stored procedure will work properly. Can somone chek this out? I would test it myself, but I don't want to delete anything just yet. It is supposed to determine the earliest year and month from the Orders table and then delete all with that variable. Here is my code:

use Northwind
go
create procedure dbo.pDeleteOldestOrders
as
begin
declare @MinYear varchar(20)
declare @delete varchar(20)
set @MinYear = (select year(min(OrderDate))
from Orders)
set @delete = delete @MinYear from Orders
end

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2008-07-22 : 22:21:22

That won't work.

set @MinYear = (select year(min(OrderDate))from Orders)

delete from Orders
where year(OrderDate) = @MinYear

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-23 : 03:20:39

or

delete from Orders
where year(OrderDate) = (select year(min(OrderDate))from Orders)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 04:28:55

Well, OP wrote "oldest Year AND month". Not the oldest full year.

CREATE PROCEDURE dbo.pDeleteOldestOrders
AS

SET NOCOUNT ON

DECLARE @MinYearMonth DATETIME

-- Get oldest orderdate
SELECT	@MinYearMonth = MIN(OrderDate)
FROM	Orders

-- Calculate first day of next month
SET	@MinYearMonth = DATEADD(MONTH, DATEDIFF(MONTH, '18991231', @MinYearMonth), '19000101')

-- Delete all records prior to this date
DELETE	
FROM	Orders
WHERE	OrderDate < @MinYearMonth

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 04:30:24

You can replace the DELETE part with a SELECT part to see that it is working.

DELETE SELECT	*
FROM	Orders
WHERE	OrderDate < @MinYearMonth

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources