CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-insert
Andre Pechio
Andre Pechio

Reputation: 100

Stored Procedure does not insert data

I am going nuts with a SQL Server stored procedure, it is supposed to register an user into the database (If the user does not already exists). But, after successfully executing the procedure, no data is inserted into the Users table.

If I run the insert statement directly, it works.

Below is the full procedure code, before you ask me, the database is empty.

--USERS
CREATE PROCEDURE [dbo].[RegisterUser]
  @NAME VARCHAR(255),
  @PHONENUMBER VARCHAR(255),
  @STATUS INT OUT,
  @REMAININGDAYS INT OUT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE Users WITH (serializable) 
    SET Name = @NAME
    WHERE PhoneNumber LIKE @PHONENUMBER

    SET @REMAININGDAYS = 0

    IF @@rowcount = 0
    BEGIN
        INSERT INTO Users (Name, PhoneNumber, RegisterDate) 
        VALUES (@NAME, @PHONENUMBER, GETDATE())

        SET @STATUS = 0
        SET @REMAININGDAYS = 40
    END
    ELSE
    BEGIN
         DECLARE @USERID BIGINT
         DECLARE @EXP DATETIME

         SELECT TOP 1 
             @USERID = USERID 
         FROM USERS 
         WHERE PhoneNumber LIKE @PHONENUMBER

         SELECT TOP 1 
             @EXP = DATEADD(day, DAYS, REGISTERDATE) 
         FROM SUBSCRIPTIONS
         WHERE USERID = @USERID 
         ORDER BY [REGISTERDATE]

         IF @EXP IS NULL
         BEGIN
             SELECT TOP 1 
                 @EXP = DATEADD(day, 40, REGISTERDATE) 
             FROM USERS 
             WHERE USERID = @USERID

             IF GETDATE() < @EXP
             BEGIN
                 SET @STATUS = 0
                 SET @REMAININGDAYS = DATEDIFF(day, GETDATE(), @EXP)
             END
             ELSE
             BEGIN
                 SET @STATUS = -1
             END
         END
         ELSE
         BEGIN
             IF GETDATE() < @EXP
                SET @STATUS = 1
             ELSE
                SET @STATUS = -1
         END
    END
END

I call it passing all parameters.

Thank you!

Upvotes: 1

Views: 1347

Answers (1)

Shannon Severance
Shannon Severance

Reputation: 18410

Statements that make a simple assignment always set the @@ROWCOUNT value to 1. @@ROWCOUNT (Transact-SQL)

So

DECLARE @i int
SET @i = 0
PRINT @@ROWCOUNT

prints 1.

DECLARE @RC INT
UPDATE Users WITH (serializable) SET Name = @NAME
WHERE PhoneNumber LIKE @PHONENUMBER
SET @RC = @@ROWCOUNT

SET @REMAININGDAYS = 0

IF @@RC = 0
BEGIN
   INSERT INTO Users  <etc...>

Or move SET @REMAININGDAYS = 0 above the update statement so nothing between the update and the test of @@ROWCOUNT.

Upvotes: 6

Related Questions