return identity column value from Table after inserting row

[NareshN]

Hi All,


I am inserting data into Employee table using stored procedure.In employee table EmpNo is identity column.Once i insert data into table stored procedure should EmpNo i.e identitycolumn value.

Please correct my stored procedure to returm EMpNo Identity column after inserting row.

alter procedure SAR_Sp_AddEmployee(@EmpName varchar(10),@CampaignID int,@startDate datetime)
as 
 insert into EmpMaster values(@EmpName,@CampaignID,@startDate)

[Frinavale]

What database management system are you using?

-Frinny

[mzmishra]

In sql server it is

alter procedure SAR_Sp_AddEmployee(@EmpName varchar(10),@CampaignID int,@startDate datetime)
    as 
     insert into EmpMaster values(@EmpName,@CampaignID,@startDate)
    SELECT SCOPE_IDENTITY()

[NareshN]

Hi Frinavale,

I am using sqlserver 2005
I got solution for that

[NareshN]

Hi,

This code is working for me.

declare @InsertedEmpID int
 SET @InsertedEmpID = SCOPE_IDENTITY()
SELECT @InsertedEmpID

Thanks

[Frinavale]

NareshN, could you please mark mzmishra's reply (post #3) as the answer to your question?

Thanks,

-Frinny