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

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

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.

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

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#

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.

-- =============================================
-- 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

**CREATE OR REPLACE PROCEDURE DELETE_ANY (ENO NUMBER)

IS

BEGIN

DELETE FROM EMP1

WHERE EMP_ID = ENO;

END;

CALL

EXECUTE DELETE_ANY(1);