CODEWITHSUNDEEP
sql-serverstored-proceduresssms
jamesss
jamesss

Reputation: 97

Stored procedure does not update the table

I want a stored procedure to calculate the age from the birthdate.

Here is my stored procedure:

CREATE PROCEDURE CalculateAge
AS
    UPDATE Person
    SET Age = DATEDIFF(year, GETDATE(), BirthDate) 
    WHERE Age = Null;

Here is my table 'Person'

CREATE TABLE [dbo].[Person]
(
    [Id] [int] NOT NULL,
    [BirthDate] [date] NOT NULL,
    [Email] [nvarchar](150) NOT NULL,
    [Lastname] [varchar](50) NOT NULL,
    [Firstname] [varchar](50) NOT NULL,
    [Age] [nchar](10) NULL,

    CONSTRAINT [PK_Person] 
        PRIMARY KEY CLUSTERED 
)

However when I execute the procedure, table with 'Person' does not change. It does not fill the column with Age...

What might be the problem?

Upvotes: 0

Views: 53

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

One obvious problem is:

UPDATE Person
    SET Age = DATEDIFF(year, GETDATE(), BirthDate)
    WHERE Age = Null;
    ----------^

The equality always evaluates to NULL (even when age is NULL). And WHERE clauses filter out NULL results. That part can be fixed using:

UPDATE Person
    SET Age = DATEDIFF(year, GETDATE(), BirthDate)
    WHERE Age IS Null;

You will next find that all the ages are negative, but that is the code you have included. Assuming that BirthDates are in the past, you want the arguments in the opposite order.

Your CREATE TABLE also doesn't work, because the PRIMARY KEY constraint is not correct. However, the table seems to exist, so I assume that is a transcription error in writing the question.

Upvotes: 4

Related Questions