CODEWITHSUNDEEP
sql-serverstored-procedurescursor
user2363591
user2363591

Reputation: 3

SQL Server stored procedure / cursor

I wanted to know if this is possible to do in SQL Server:

My table looks like this:

 CREATE TABLE MEMBERSHIP
(
    Memebership_ID INT IDENTITY(1,1)PRIMARY KEY NOT NULL,   
    MemberShip_Expiry_Date Datetime,
    Member_Account_Balance Money,
    Member_Blacklisted bit ,--(0 for no, 1 for yes)
    Customer_ID INT not null,
    Last_Payment datetime
)

I wanted to know if it is possible to use a stored procedure with or without a cursor inside of it to change the Member_Blacklisted column if the Last_Payment was more than 6 months from the date that has to be inserted e.g.

DECLARE @MemberID int,@Date datetime

My attempt so far:

DECLARE @MemberID int,@Date datetime

-- Declaring the Cursor. 
DECLARE C_Expired_Penalty_BlackList CURSOR
FOR
(
 SELECT 
 FROM MEMBERSHIP
)

-- Open the Cursor declared.
OPEN C_Expired_Penalty_BlackList
FETCH NEXT FROM C_Expired_Penalty_BlackList INTO @MemberID,@Date
WHILE @@FETCH_STATUS = 0
BEGIN
 IF @Date > DATEPART(MONTH,getdate()+6)
 BEGIN
  update MEMBERSHIP
  set Member_Blacklisted = 1
  Where Memebership_ID = @MemberID 
 END
  FETCH NEXT FROM C_Expired_Penalty_BlackList INTO @MemberID,@Date
END

CLOSE C_Expired_Penalty_BlackList
DEALLOCATE C_Expired_Penalty_BlackList

Upvotes: 0

Views: 3554

Answers (1)

beezir
beezir

Reputation: 470

Try this:

UPDATE MEMBERSHIP 
SET Member_Blacklisted = 1 
WHERE Last_Payment < DATEADD(Month, -6, GETDATE())

Upvotes: 4

Related Questions