Which is the best way to get the Id of last inserted record

See more:

SQL

SQL-Server

Hello Everyone,

I am having a doubt and need your opinion on that.

I am having two tables

1) TenantDetails

CREATE TABLE [dbo].[TenantDetails](
    [TenantId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](20) NULL,
    [LastName] [varchar](20) NULL,
    [DOB] [date] NULL,
    [Occupation] [varchar](20) NULL,
    [Organisation] [varchar](50) NULL,
    [Address] [varchar](50) NULL,
    [City] [varchar](20) NULL,
    [State] [varchar](20) NULL,
    [ountry] [varchar](20) NULL,
    [PhoneNo] [varchar](13) NULL,
    [MobileNo] [varchar](13) NULL,
    [IDType] [varchar](30) NULL,
    [IDNumber] [varchar](20) NULL,
    [TenantStatus] [bit] NOT NULL,
    [CreatedOn] [datetime] NOT NULL,
    [CreatedBy] [varchar](20) NOT NULL,
    [ModifiedOn] [datetime] NULL,
    [ModifiedBy] [varchar](20) NULL,
PRIMARY KEY CLUSTERED
(
    [TenantId] ASC
)
)

2) TenentRoomMapping

CREATE TABLE [dbo].[TenantRoomMapping](
    [TenantId] [int] NOT NULL,
    [RoomId] [int] NOT NULL,
    [CheckinDate] [date] NOT NULL,
    [CheckOutDate] [date] NULL,
    [Status] [bit] NOT NULL,
    [SecurityDeposity] [int] NOT NULL,
    [Remarks] [varchar](max) NULL,
    [CreatedOn] [datetime] NOT NULL,
    [CreatedBy] [varchar](20) NOT NULL,
PRIMARY KEY CLUSTERED
(
    [TenantId] ASC,
    [RoomId] ASC,
    [Status] ASC
)
)
ALTER TABLE [dbo].[TenantRoomMapping]  WITH CHECK ADD FOREIGN KEY([RoomId])
REFERENCES [dbo].[Rooms] ([RoomID])


ALTER TABLE [dbo].[TenantRoomMapping]  WITH CHECK ADD FOREIGN KEY([TenantId])
REFERENCES [dbo].[TenantDetails] ([TenantId])

And My requirement is -

when I add a new tenant's details in TenantDetails, I will assign a room to that tenant.

In this case I am using two SPs.

1) Usp_AddTenant (this Sp is inserting record in tenant details and returning the id of last added record (TenantId, which is auto generated and primary key))

2) Usp_AssignRoom( this is inserting record in RoomId(passed from front end), TenantId(returned from usp_Addtenant), and other details)

Code and functionality wise everything working fine.

i doubt is in SP "Usp_AddTenant"

it looks like...

create proc Usp_AddTenant
(
inputparameters,
@out int output
)
as
begin

insert statement ....

set @out= (select Max(TenantId) from TenantDetails(nolock) where Createdby= @createdby)

end

this application will run on intranet and multiple users will be there.. so my question is
which way should use for getting id of last inserted record in tenant details

set @out= (select Max(TenantId) from TenantDetails(nolock) where Createdby= @createdby)

note : createdby is the userid of user logged in
or

set @out= (SELECT SCOPE_IDENTITY())

or

set @out= SELECT IDENT_CURRENT('TenantDetails')

I have doubt that there should not be mixing of tenant id in case when two users are working on same page from diff. system.

I hope I have provided all the required info. and if still you want any other info do let me know...

Thanks in advance....