Storing null if no data exists on table instead 1000

i used this sp , to insert values , i need to start with 1000 , if there is no values present in the sid column in the table ,
but in this procedure,  if there is no data in the table it stores NULL in sid column , then it starts with 1001 and so on ...



create procedure sptbl
(@id int,@name nvarchar(50)) 
AS
INSERT  tbl1(sid,name)
SELECT(SELECT MAX(ISNULL(sid, 1000)) + 1 FROM tbl1),
@name

Open in new window

LVL 18
Rajar AhmedConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GuitarRichCommented:
Is this to create a new incrementing ID field in the table?

If so this is a bad way to do it anyway - if 2 users hit the stored procedure at the same time, both will get the same ID and conflict with each other.

It would be better to make the sid field an IDENTITY and seed it at 1000 - that will then start at 1000 and each new record will get the next number in the sequence.
0
Rajar AhmedConsultantAuthor Commented:
nono,i have a seperate identity column in this table , this is for different use .

this column is used to increment when the sid is not exits.which i do in .net coding using session concepts but its a incrementing field only  anyway

any other idea to avoid ?
.



0
GuitarRichCommented:
try changing the max isnull bits around like this:

create procedure sptbl
(@id int,@name nvarchar(50)) 
AS
INSERT  tbl1(sid,name)
SELECT(SELECT ISNULL(MAX(sid), 1000) + 1 FROM tbl1),
@name

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.