SQL Server stored procedure / cursor

Question

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

Check out beezir's answer. It will do what you want. Currently your date comparison is nonsensical because you're comparing a date to an int. When you do this you're comparing the number of days since January 1st, 1900 against a number between 1 and 12. You're comparison check will always be true. — Eric J. Price
– Eric J. Price, Commented May 8, 2013 at 19:05

beezir · Accepted Answer · 2013-05-08 18:54:00Z

4

Try this:

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

answered May 8, 2013 at 18:54

beezir

4703 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Eric J. Price Over a year ago

I think using DatePart is a bad idea here as they can range anywhere from 6 to just under 7 months late, but considering that is the logic the OP was using this is right.

beezir Over a year ago

@Love2Learn Yeah, I just copied the logic from the question. Changed it to use DATEADD instead.

Eric J. Price Over a year ago

Yeah, nice, I think the OP's logic is actually complete wrong because they're returning an int associated to the month six months in the future and comparing it against the Last_Payment which is really non-sensical. I think this is what they want.

user2363591 Over a year ago

Sorry if i sound dumb but what do you suggest must i change?

Eric J. Price Over a year ago

@user2363591 Use the above update in place of the entirety of the code you have in your post.

|

Collectives™ on Stack Overflow

SQL Server stored procedure / cursor

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related