CODEWITHSUNDEEP
sqlsql-serverstored-proceduressql-insert
ricky
ricky

Reputation: 75

SQL Stored Procedure Insert value based on id

I want to run a stored procedure that uses the self generated ID to populate a value in another column but in the same row.

EX:

Table: ID, Reference

ID is auto number, Reference is a VARCHAR made of ID-CURRENTDATE.

Thus if the ID is 230, for the insert statement, the Ref should be 230-20130225.

Is this possible, and how can I achieve this?

Upvotes: 0

Views: 1397

Answers (1)

Kaf
Kaf

Reputation: 33809

If you are on sql-server, you could do this with a computed column.

create table myTable ( 
    id int identity (1,1) not null,
    name varchar(50) not null,
    recordDate date default getdate(),
    reference  as ((convert(varchar,[id1],(0)) + '-') + convert(varchar,recordDate,(112)))
)

And then your insert would be like (or you can enter a recordDate)

insert into myTable (name)
values ('xyz')

If you can't alter the table then try (in a stored procedure);

declare @recordId int
insert into myTable (name)
values ('xyz')

select @recordId = scope_identity()

update myTable set reference = convert(varchar, @recordId) + '-' +  
                               convert(varchar, getdate(), 112)
where id = @recordId

Upvotes: 1

Related Questions