sql server procedure

kambozr · #1 (**permalink**) 01-25-12, 06:31

Hi guys, I am new to stored procedures and have tried to get stuck into this task, but I am completely stuck.

What is required?

The object will be run as part of the nightly batch and will delete records from the interfacelog table. It will keep the number of records as specified in the parameters. If the parameters are less than 7, they will get reset to 7 to ensure there is always one week’s worth of historical data kept.

DELETE FROM interfacelog

WHERE datetime < (current timestamp – N days)

AND status = 1

The procedure will run as follows:

1. Check that the number of successful days is less than 7, if not set to 7, if greater than 7 set the variable to the parameter.
2. Delete the successful records from the table
3. Check that the number of failed days is less than 7, if not set to 7, if greater than 7 set the variable to the parameter.
4. Delete the failed records from the table.

With regards to the above, records are inserted in a table that I am using. The table will get very large and requires to be truncated on a regular basis.
Two parameters include - specify how many days worth of successful records which is 'Status 1', 2ND parameter specify how many days worth of failed records which is 'status 2'

Status is a field within the table i am using.

I have two input variables known as IN @success and IN @fail, both decimal data types.

I really hope you guys could help me out.

Thank you in advance

Ram

Wim · #2 (**permalink**) 01-25-12, 07:34

I wouldn't worry too much about getting your code wrapped in a PROCEDURE. Just start writing the T_SQL to get your job done. Add a number of PRINT statements so you can verify your program. Only at the end, when all works, add CREATE PROCEDURE in front. (And remark or remove the PRINT statements.)

This will get you started:

Code:

DECLARE @success INT	-- number of days to keep with Status = 1
DECLARE @fail INT	-- number of days to keep with Status = 2

SET @success = NULL
SET @fail = 0

DECLARE @LastSuccessTimestamp DATETIME	-- interfacelog records (Status = 1) 
					-- with a timestamp before this will be deleted
DECLARE @LastFailTimestamp DATETIME	-- interfacelog records (Status = 2) 
					-- with a timestamp before this will be deleted

PRINT '@success = ' + COALESCE(CAST(@success as VARCHAR(10)), 'NULL')
PRINT '@fail = ' + COALESCE(CAST(@fail as VARCHAR(10)), 'NULL')

If COALESCE(@success, 1) < 7 
	SET @success = 7
	
If COALESCE(@fail, 1) < 7 
	SET @fail = 7

PRINT '@success = ' + CAST(@success as VARCHAR(10))
PRINT '@fail = ' + CAST(@fail as VARCHAR(10))

SET @LastSuccessTimestamp = DATEADD (day , -@success , GETDATE())
PRINT 'GETDATE() = ' + CAST(GETDATE() as VARCHAR(20))
PRINT '@LastSuccessTimestamp = ' + CAST(@LastSuccessTimestamp as VARCHAR(20))

DELETE FROM interfacelog
WHERE datetime < @LastSuccessTimestamp
	AND status = 1

....

Internet Services

Web Development

Digital Marketing

Consumer Tech