CODEWITHSUNDEEP
sql-server
Joseph Nields
Joseph Nields

Reputation: 5661

How can I have a column auto generated on inserts and updates as a function of other columns?

In a nutshell, here's what I'm trying to do:

create function Composite(@year int, @major int, @minor int)
returns int
    begin
        return(select(@year*10000 + @major*100 + @minor))
    end
go

create table AutoGeneratedColumn (
    [year] int,
    [major] int,
    [minor] int,
    [composite] int default Composite([year], [major], [minor])
)

(This doesn't work.)

I want the column composite to be a composite value of year, major, and minor, such that composite is always equal to Composite(year, major, minor). Ideally, explicit values wouldn't be able to be supplied on inserts, and updates should update composite as well. I'm not sure how to do this, though.

Where should I be looking?

Upvotes: 0

Views: 31

Answers (3)

Steve Ford
Steve Ford

Reputation: 7763

Use a computed column:

create table AutoGeneratedColumn (
    [year] int,
    [major] int,
    [minor] int,
    [composite] as ([year]*10000 + [major]*100 + [minor])
)

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33581

You shouldn't use a function for this. This is a basic computed column.

create table AutoGeneratedColumn (
    [year] int,
    [major] int,
    [minor] int,
    [composite] as ([year]*10000 + major*100 + minor)
)

Upvotes: 1

Brian Pressler
Brian Pressler

Reputation: 6713

It's called a computed column. You don't need the function call.

create table AutoGeneratedColumn (
    [year] int,
    [major] int,
    [minor] int,
    [composite]  AS ([year] * 10000 + [major] * 100 + [minor]),
)

Upvotes: 2

Related Questions