We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Use variable in SQL script

Medium Priority
364 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

Comment
Watch Question

Senior Technical Lead
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Kalyanum Deepak KumarSenior Technical Lead

Commented:
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.

Author

Commented:
Hi Deepak,
Thank you for your quick response.  Than this SP can be scheduled directly?
Thank you again.
Kalyanum Deepak KumarSenior Technical Lead

Commented:
Yes, this can be scheduled by placing it as a step in sql server agent job.

Author

Commented:
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
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.
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

Author

Commented:
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.
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

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
It is upto you. If need to purge data frequently  than create SP otherwise no need.
Kalyanum Deepak KumarSenior Technical Lead

Commented:
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
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.