Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Unanswered: sql server procedure

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    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
    
    ....
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •