can an INSERT statement RETURN a value?

I only know a little bit of SQL for Access databases, so sorry if this is a silly question!

Situation: I've to INSERT a new company in a SQL server table named 'companies'. This new company will automatically receive a unique ID (autonumber in Access terminology, don't know how to call it in SQL server)

Question: Can this insert statement return the ID it gave to the company? Or how do I get this ID to use it in an other table?

Thanks in advance!create proc <blah-blah> (@.blah1 varchar(blah), @.blah2 varchar(blah-blah) )
as
declare @.RetVal int, @.Error int
begin tran
insert <blah> (blah1, blah2) values (@.blah1, @.blah2)
select @.Error = @.@.error, @.RetVal = scope_identity()
if @.Error != 0 begin
raiserror ('failed to insert into blah', 16, 1)
rollback tran
return 1
end
commit tran
select NewIdentityValue = @.RetVal
return 0

OR, you can define @.RetVal as output parameter, this way you won't have to do a final SELECT. It's all up to your taste and preference.|||...in other words, NO, it can't, but you can put your Insert statement in a procedure that will return a value or an Output parameter.

blindman|||it actually appears that the answer is YES. explanation blindman?|||We're splitting hairs... yes you can get the ID... from the SELECT statement, no... from a stored procedure... yes...

ClipChips asked if the statement could return the ID. The statement itself cannot. But if you use a SP, you can retrieve it either as a SELECT to a recordset, or an OUTPUT parameter.|||It sounds like the answer is really our favorite "yes and no". Yes, @.@.error acts as a sort of return value, but in the strict definition of a return value, you can not have
exec @.retvalue = "insert into table values (...)" Does that explain it better?|||Yes, No?

You can get Id back without stored procedure!

create table test(id int identity primary key
,code varchar(10))
go
create trigger ins_test on test
for insert
as
select id from inserted
go
insert test values('A')
go
id
----
1

Just get recordset from command object...|||True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)|||Originally posted by Seppuku
True, but you need a trigger instead.. :)... 6 and half a dozen.. take your pick :)

But it is possible... ;)|||you can do it even without a trigger, but from a command object.

create table test1 (f1 int identity(1,1) not null, f2 char(1) not null)
go

Just assign the following command to it:
insert test1 (f2) values ('A') select RetVal=@.@.identity -- or scope_identity() for sql2k