Help with a simple stored procedure for date

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)

Help with a simple stored procedure for date

Author

Topic

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-08-27 : 13:19:48

Hi All,

I have a very basic stored procedure(see below in bold). It delete all records from two tables where the date is today's date or greater.
I want to change the stored procedure in a way that while executing the procedure if a user defines a particular date it will delete all the records from that date to all other greater dates. Otherwise If a user don't define a date it will delete all records from today's date and greater.

Here is the cide for my stored procedure.

CREATE PROCEDURE z_DeleteRecords
AS
BEGIN
SET NOCOUNT ON;
--Step 1
--Remove records from table1
DELETE FROM table1
WHERE Date>=CONVERT(VARCHAR(10), GETDATE(), 120)

--Step 2
--Remove records from table2
DELETE FROM table2
WHERE Date>=CONVERT(VARCHAR(10), GETDATE(), 120)

END
GO

So for example lets say today is 2009-08-27;
so the following would delete all records, where the date is equal to or greater than '2008-01-01':
Exec z_DeleteRecords ('2008-01-01')

Whereas the following would delete all records from today's date to greater:
Exec z_DeleteRecords ()

How to make this change in my stored procedure.

Thanks for your quick help.

Zee

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 13:24:23

[code]
CREATE PROCEDURE z_DeleteRecords
@DateVal datetime=NULL
AS
BEGIN
SET NOCOUNT ON;
SET @DateVal=COALESCE(@DateVal,GETDATE())
--Step 1
--Remove records from table1
DELETE FROM table1
WHERE Date>=DATEADD(dd,DATEDIFF(dd,0,@DateVal),0)

--Step 2
--Remove records from table2
DELETE FROM table2
WHERE Date>=DATEADD(dd,DATEDIFF(dd,0,@DateVal),0)

END
GO

[/code]

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-08-27 : 13:24:43

[code]
CREATE PROCEDURE z_DeleteRecords @FromDate as Datetime
AS

select @fromDate = coalesce(@fromdate,getdate())

BEGIN
SET NOCOUNT ON;
--Step 1
--Remove records from table1
DELETE FROM table1
WHERE Date>=CONVERT(VARCHAR(10), @fromDate, 120)

--Step 2
--Remove records from table2
DELETE FROM table2
WHERE Date>=CONVERT(VARCHAR(10), @fromdate, 120)

END
GO
[/code]

There's one way.

An infinite universe is the ultimate cartesian product.

zeeshan13
Constraint Violating Yak Guru

347 Posts

Posted - 2009-08-27 : 13:27:54

Thanks guys. I haven't tried it yet.
But visakh16 is there a reason why you change my today's date function?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 13:29:54