I looked at your proc, and have a few concerns.

1) You state that it handles the multi-user environment by generating and updating the next number in the same stored procedure.  Incorrect.  Your not using transactions.  Under a heavy load it is possible for two users to select from the table before either updates it

2) Use of dynamic sql.  Come on, do you really need to use it here!

Sorry, but I think that there is plenty of room for improvment here.

Thankx for the reply

1. My concern is not about the accessing. but getting same no for both users.

Say User A Read and get number 15 before updating it user B also gets 15

I want to stop that.

2. What do u mean by dynamic sql.

and do u have any other way of doing this. because I'm looking for this kind of solutions

Thankx again for the reply

This part is Dynamic SQL:

Select @sql = "Update " + @Tablename + " Set LastNumber ="

+ Cast(@LastNumber as varchar(10)) +

" Where CODE ='" + @secondType + "'"

execute (@sql)

You need to have the "selecting, incrementing & updating" process happen for one person at a time.  You can use a transaction and locking to do this, for example...

 --------------------------------------------------------------------------------------
 commit transaction
 --------------------------------------------------------------------------------------


Alternatively, without using a transaction at all, add the old value of the "Next Number" to the where clause of the update and check the rowcount.  If you haven't updated any rows then you need to get the next number.

My argument is if both users are using same type. they will get same number there can be duplicate number unless u update it just after reading.

What?  Sorry, but I'm not sure of your requirement.

Are you saying that if two users are using the same type, it is OK for them to have duplicate numbers?

NO SIR,

If I didn't update just after reading there is chance of getting same number for both records.

To avoid that I am updating the Next number Just after reading.

By putting the update straight after the read is enough.  You need to do more.  Check this script out to illustrate my point...

use tempdb
create table Numbers (LastNumber int not null, primary key(LastNumber))
insert into Numbers values (0)


Now create 2 connections to the database and run the following script on each one at the same time.  (This will simulate 20000 requests for next number in a short period of time)

set nocount on

declare @LastNumber int
declare @NewNumber int
declare @Count int
declare @Index int

select @Count=10000, @Index=1

while (@Index <= @Count)
begin
    Select @LastNumber = LastNumber From Numbers

    Select @NewNumber = @LastNumber + 1

    Update Numbers Set LastNumber = @NewNumber

    select @Index= @Index + 1
end

Select LastNumber From Numbers

When I run this on my local tempdb, I get the result as 17209. This means that there were 2791 duplicate next numbers generated!  Obviously you may not have the demand of 20000 instantaneous requests, but it only needs to happen once to corrupt your data integrity.

Now change the script to either use transactions, or include the LastNumber in a where clause of the update statement (as mentioned in my previous post) to correct the problem.

I got your point now

Thankx

I thought u were saying some error in my method.

your request is an improvement which is welcome.

And for second question

 y are against with dynamic SQL

in my case I have to use it. What are the altenatives u have.

Thankx again for the reply