Click here to Skip to main content
15,614,943 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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,
    [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,
    [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
@out int output

insert statement ....

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


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



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....
Updated 24-Jul-13 0:47am

The best way is to use @@IDENTITY[^]

Please, read this article: SCOPE_IDENTITY (T-SQL)[^] to understand the difference between @@IDENTITY and SCOPE_IDENTITY.
Share this answer
Adarsh chauhan 24-Jul-13 7:06am    
Thanks Maciej ...

I already gone through this link.. but my question is a bit different...

I mean there are two users let's say A and B
both are using same page...
both will insert records... at the same time SP usp_AddTenant will be called...
so if i use @@IDENTITY or scope_identity... it should not provide the id of last inserted id of record inserted by other user...
if it does that wrong room no will get assigned to tenant...

so should i use (select Max(TenantId) from TenantDetails(nolock) where Createdby= @createdby) in my case..?? or @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT will work right for my case??
Maciej Los 24-Jul-13 7:30am    
In my opinion it's not possible to insert data in the same time. Even if you think that 2 users call SP in the same time, there are miliseconds between both calls. Yes, i think that @@identity or Scope_Identity should works perfect in your case.
Herman<T>.Instance 24-Jul-13 7:33am    
I believe that too. No 2 inserts on the same time but Always 1 after another.
Adarsh chauhan 24-Jul-13 7:36am    
ya you are right.. sorry my sentence conveyed wrong message.. by that didn't mean that they will be on same time.. i was asking that whether id will be returned session wise or table wise...
Adarsh chauhan 24-Jul-13 7:31am    
Thanks a lot, I got my answer..
I used all the cases...
select case(which i was using),@@Identity and Scope_identity() is working fine for me... but Ident_current() would not work right in my case..

Thanks for help... :)
You could also modify the primary key to
[TenantId] DESC

When you do a select top 1 from TenantDetails(nolock) where Createdby= @createdby, you will Always have the most recent one
Share this answer
Adarsh chauhan 24-Jul-13 7:00am    
that is not a prob. if i use where Createdby= @createdby i will get the desired output...
i mean if i dont use where clause..
like suppose at the same time two users are using same app. respectively ids generated are 1,2 i dont want it to happen that id generated by 1st user's trans. returned to 2nd or vise versa..
Herman<T>.Instance 24-Jul-13 7:07am    
using MAX(tenantID) is slower than TOP 1. If you get a real large volume in your tables TOP 1 is the first row from table. Add Index on created By too!
Adarsh chauhan 24-Jul-13 7:10am    
thats fine i will use Top 1 order by desc... this will do my task..
but my question is...
(select Top 1 TenantId from TenantDetails(nolock) where Createdby= @createdby Order by TenantId desc) would be better in my case or @@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT will work right ??
Herman<T>.Instance 24-Jul-13 7:13am    
@@IDENTITY is best use. This gives the value from your current sql insert statement. I do not know the performance of @@IDentity in case of high volumes. That is why I use TOP 1.
Herman<T>.Instance 24-Jul-13 7:16am    
By the way Have you tested the situation ? Then you can tell teh differences by experience

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900