CODEWITHSUNDEEP
sql-server
HelpASisterOut
HelpASisterOut

Reputation: 3185

SQL - Adding IF condition to my stored procedure

I have a stored procedure where I Increment the sub_days value daily,

I want to add a condition where

if sub_days = 30 then sub_months=sub_months+1 and sub_days=0

How to add this condition to my stored procedure?

My SP:

    ALTER PROCEDURE [dbo].[Update_Users_With_Month] 
     @ID bigint


    AS
    BEGIN

       update Subscribers_Profile 
       set Sub_Updated = GETDATE() , sub_days = sub_days+1
       where sub_ID = @ID


**add condition here**

    END

Upvotes: 2

Views: 19955

Answers (4)

S SenthamizhMani
S SenthamizhMani

Reputation: 1

Try this.

ALTER PROCEDURE [dbo].[Update_Users_With_Month] @ID bigint AS BEGIN 
 SET Sub_Updated = GETDATE() ,
                 sub_days = sub_days+1
  WHERE sub_ID = @ID 

  DECLARE  @sub_days  INT;
    SET @sub_days  = (SELECT sub_days FROM dbo.Subscribers_Profile WHERE sub_ID = @ID);
     IF @sub_days =30 

  BEGIN
      UPDATE Subscribers_Profile
      SET sub_months = sub_months +1 ,
      sub_days=0 WHERE sub_ID = @ID ;

   END 

  ELSE 


    END

Upvotes: 0

Suraj Singh
Suraj Singh

Reputation: 4059

ALTER PROCEDURE [dbo].[Update_Users_With_Month] @ID bigint AS BEGIN
UPDATE Subscribers_Profile
SET Sub_Updated = GETDATE() ,
                  sub_days = sub_days+1
WHERE sub_ID = @ID 

  DECLARE  @sub_days  INT;
  SET @sub_days  = (SELECT sub_days FROM dbo.Subscribers_Profile WHERE sub_ID = @ID);
IF @sub_days =30 

BEGIN
  UPDATE Subscribers_Profile
  SET sub_months = sub_months +1 ,
      sub_days=0 WHERE sub_ID = @ID ;

 END 

ELSE 


END

Upvotes: 0

Jade
Jade

Reputation: 2992

TRY THIS

THIS MIGHT BE YOUR LOOKING FOR BASE ON YOUR SAMPLE CODE

 ALTER PROCEDURE [dbo].[Update_Users_With_Month] 
 @ID bigint
AS
BEGIN
Declare @Sub_Days int
Declare @sub_months int

SELECT @Sub_Days = sub_days + 1, @sub_months = sub_months
FROM Subscribers_Profile
WHERE sub_ID = @ID

If @Sub_Days >= 30
BEGIN
    SET @Sub_Days = 0;
    SET @sub_months = @sub_months + 1
END

UPDATE Subscribers_Profile 
    SET Sub_Updated = GETDATE(),
    sub_months = @sub_months,
    sub_days=@Sub_Days
WHERE sub_ID = @ID

END

Upvotes: 0

Kahn
Kahn

Reputation: 1660

Something like this?

ALTER PROCEDURE [dbo].[Update_Users_With_Month] 
@ID bigint

AS
BEGIN

   update Subscribers_Profile 
   set Sub_Updated = GETDATE()
      , sub_days = CASE WHEN sub_days+1 >= 30 THEN 0 ELSE sub_days+1 END
      , sub_months = CASE WHEN sub_days+1 >= 30 THEN sub_months+1 ELSE sub_months END
   where sub_ID = @ID

END

And in case you have NULLs in sub_months you can just use ISNULL in the case, like:

... THEN ISNULL(sub_months,0)+1 ELSE sub_months END ...

Upvotes: 3

Related Questions