?
Solved

Use variable in SQL script

Posted on 2011-04-21
13
Medium Priority
?
346 Views
Last Modified: 2012-05-11
Hi Experts,
This is first time I am working on scheduling on job on SQL Server 2005.  I want to delete all the data in the table more than 6 months old.  I need to use a variable in stead of the #6.  Do I need to write a Stored Procedure?  Please help?  Many thanks in advance.

delete FROM dbo.TICKETS WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > 6
delete FROM dbo.PNR WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > 6
delete FROM dbo.PNRS_LOGS where DATEDIFF(MONTH, LOG_DATE, GETDATE()) > 6

0
Comment
Question by:RadhaKrishnaKiJaya
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 4

Accepted Solution

by:
Kalyanum Deepak Kumar earned 1000 total points
ID: 35443375
yes, a stored procedure with an input parameter should serve the purpose.

Here is how it is done:

CREATE PROC spDelete @Months INT
AS
BEGIN
delete FROM dbo.TICKETS WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > @Months
delete FROM dbo.PNR WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > @Months
delete FROM dbo.PNRS_LOGS where DATEDIFF(MONTH, LOG_DATE, GETDATE()) > @Months
END
0
 
LVL 4

Expert Comment

by:Kalyanum Deepak Kumar
ID: 35443384
Then the stored procedure can be executed as spDelete 6/7/8/9. Whatever value which is provided here reflects in the sql inside stored procedure and thus configurable.
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 35443414
Hi Deepak,
Thank you for your quick response.  Than this SP can be scheduled directly?
Thank you again.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 4

Expert Comment

by:Kalyanum Deepak Kumar
ID: 35443607
Yes, this can be scheduled by placing it as a step in sql server agent job.
0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 35443881
Deepak, Does this look okay to you?  Thank you.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spDelete]
    @Months      Int
AS
BEGIN
    SET NOCOUNT ON;
    SET @Months = 6
    delete FROM dbo.TICKETS WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > @Months
    delete FROM dbo.PNR WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > @Months
    delete FROM dbo.PNRS_LOGS where DATEDIFF(MONTH, LOG_DATE, GETDATE()) > @Months
END
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35443905
You need to pass in the @Months variable rather than set it within the stored procedure.  However, if you want to set it within the stored proc, then remove the @Months from between the ALTER and the AS lines.
0
 
LVL 6

Expert Comment

by:LCSandman8301
ID: 35443914
as a suggestion to this: it would be best if you calculated the date that you want to "delete before" ahead of time. that way the calculation doesn't mess with the execution plan so i would do something like attached:

and then in your scheduled job do the following:
declare @time datetime
set @time = dateadd(month,-6,getdate())
exec spDelete @time
CREATE PROC spDelete @beforedate datetime
AS
BEGIN
delete FROM dbo.TICKETS WHERE START_DATE < @beforedate
delete FROM dbo.PNR WHERE START_DATE < @beforedate 
delete FROM dbo.PNRS_LOGS where LOG_DATE < @beforedate
END

Open in new window

0
 

Author Comment

by:RadhaKrishnaKiJaya
ID: 35443960
I need the @Months varable, because I have lots of table to delete.  Now I have to delete all the data for 6 months.  But tomorrow it may change to 3 months.  So that I can change the @Months = 3.  Please let me know.  Thank you.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35444157
If you have to have the @Months flexible, then you probably DO need to pass it in as a variable.  Therefore, pass the number of months as an INT variable (@Months) and do not set the value within the Stored Proc.  (See below where I have commented out the SET @MONTHS line:



set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spDelete]
    @Months      Int
AS
BEGIN
    SET NOCOUNT ON;
--    SET @Months = 6
    delete FROM dbo.TICKETS WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > @Months
    delete FROM dbo.PNR WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > @Months
    delete FROM dbo.PNRS_LOGS where DATEDIFF(MONTH, LOG_DATE, GETDATE()) > @Months
END

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35446145
It is upto you. If need to purge data frequently  than create SP otherwise no need.
0
 
LVL 4

Expert Comment

by:Kalyanum Deepak Kumar
ID: 35446148
Hi RadhaKrishna,

Yes here is how it should be done :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[spDelete]
    @Months      Int
AS
BEGIN
    SET NOCOUNT ON;
    delete FROM dbo.TICKETS WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > @Months
    delete FROM dbo.PNR WHERE DATEDIFF(MONTH, START_DATE, GETDATE()) > @Months
    delete FROM dbo.PNRS_LOGS where DATEDIFF(MONTH, LOG_DATE, GETDATE()) > @Months
END
0
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 1000 total points
ID: 35447524
@DeepakKumarK,

That looks kind of familiar . . . except for the fact that you deleted the line I commented out. ;-)

@PatelAlpesh,
The author has already indicated that there is a need to purge data on a reasonably frequent basis.  That's the reason for the focus on the stored procedure.

@RadhaKrishna,
As has been observed, using a datetime to indicate the earliest date to remain in the tables would improve the performance of the SP.  Also, if you calculate that date within SP, it can improve the performance in another way in that it can preclude parameter sniffing by the query optimizer and a possible less than perfect query plan being used.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spDelete]
    @Months      Int
AS
BEGIN
    SET NOCOUNT ON;
--    SET @Months = 6
    DECLARE @DeleteDate DateTime;
    
    SET @DeleteDate = DATEADD(month, -@months, GetDate());
    SET @DeleteDate = CAST(FLOOR( CAST(@DeleteDate  AS FLOAT)) AS DATETIME);

    DELETE FROM dbo.TICKETS
    WHERE START_DATE < @DeleteDate;

    DELETE FROM dbo.PNR
    WHERE START_DATE < @DeleteDate;

    DELETE FROM dbo.PNRS_LOGS
    WHERE LOG_DATE < @DeleteDate;

END

Open in new window

0
 

Author Closing Comment

by:RadhaKrishnaKiJaya
ID: 35462315
Thank you @DeepakKumarK, PatelAlpesh, LCSandman8301 and 8080_Diver Points very much for your help and time.  You guys are simply great.  All the help you have extended here is very helpful in different scenario.  Here @DeepakKumarK suggestion worked for me the best.  Thank you all again for your quick response and please keep doing it.
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question