CODEWITHSUNDEEP
c#sql-server-2008stored-procedures
Macnique
Macnique

Reputation: 1034

write a sql stored procedure to delete the data from the table?

I have a simple application which stores the data into the sql server database table named student table .The design of the table is as follows 3 colums. name ,sex and registeredtime(datetime).In my WPF window i have three fields to insert the data into the table.Now i want to have the delete button based on the input given by the user(which is a datetimepicker).

How to delete the data from the table which is 7 days old compared to the date given by the user. ?

I want to have a stored procedure which i can call from the c# code.i am able to try some thing like this but Select * from studenttable where registereddate < GetDate()-7 but i am unable to achieve what i am supposed to ...

Upvotes: 4

Views: 31903

Answers (7)

Ahmad AL-Smadi
Ahmad AL-Smadi

Reputation: 1

**CREATE OR REPLACE PROCEDURE DELETE_ANY (ENO NUMBER)

IS

BEGIN

DELETE FROM EMP1

WHERE EMP_ID = ENO;

END;

CALL

EXECUTE DELETE_ANY(1);

Upvotes: 0

user11565652
user11565652

Reputation: 

-- =============================================
-- Author:      XXXX
-- Create date: xx-xx-xxxx
-- Description: Procedure for Deleting  From Table 
-- =============================================


CREATE PROCEDURE [dbo].[SP_Employee_Delete]
(
@EmpCode bigint=null,
@Msg nvarchar(MAX)=null OUTPUT
)
AS
BEGIN TRY

DELETE FROM tbl_Employees
WHERE EmpCode=@EmpCode

SET @Msg='Employee Deleted Successfully.'

END TRY
BEGIN CATCH
SET @Msg=ERROR_MESSAGE()
END CATCH

GO

Upvotes: 0

Chains
Chains

Reputation: 13157

CREATE PROC
(@date smalldatetime)
AS
BEGIN

DELETE studenttable 
WHERE registereddate < (@date - 7)

END

Tips:

  1. You want to avoid doing any functions on regsitereddate in your WHERE clause, so your index can still be used.
  2. @date is called a parameter. Look into parameters in your C# function as well. Other keywords there: sqlcommand, addwithvalue(), etc.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96552

DELETE ST
--SELECT * 
FROM StudentTable ST
WHERE DATEADD(day,-7,GetDate()) > registeredDate

A little trick to allow you to check the records you are going to affect inthe delte statment before running for the first time. Just run from the SElect on to check the records chosemn, then you know the delte will affect the right records before you do it.

Upvotes: 4

Matthew
Matthew

Reputation: 10444

If I read your question correctly you just replace your SELECT * FROM syntax with DELETE syntax

DELETE 
    studenttable 
WHERE 
    registereddate < @pYourDate

Then you pass in the date as the stored procedure parameter from c#

Upvotes: 1

Senad Meškin
Senad Meškin

Reputation: 13756

create procedur spDeleteByDate
@dateTo datetime
as
begin

declare @dateFrom datetime;
set @dateFrom = select DATEADD(day, -7, @dateTo)

delete from your_table where [your_date_column] between @dateFrom and @dateTo

end

I hope this helps

Upvotes: 2

Abe Miessler
Abe Miessler

Reputation: 85046

You will probably need to use the DATEADD function:

DELETE StudentTable
WHERE DATEADD(day,-7,GetDate()) > registeredDate

Make sure you do the equivalent select first to make sure you are deleting what you want:

SELECT * FROM StudentTable
WHERE DATEADD(day,-7,GetDate()) > registeredDate

Your stored proc would look something like this:

CREATE PROCEDURE DeleteRecent AS
BEGIN
    DELETE StudentTable
    WHERE DATEADD(day,-7,GetDate()) > registeredDate
END
GO

Upvotes: 7

Related Questions