SQL - generate a primary key in a stored procedure insert

Hello,

I have a situation where having the primary key of my SQL Server 2005 table set as an identity is causing problems, so I am going to turn the identity off.

Is there an easy way to have my stored procedure generate the primary key for an inserted record with a line or two of code? Obviously I want it to be an incremental number, just as if it was an identity, but rather than SQL doing it, I want to be managing this creation within the stored procedure.

Thanks!
eviglottiAsked:
Who is Participating?
 
eviglottiConnect With a Mentor Author Commented:
HI all,

I apologize, but it turns out that the application that I have is no longer having trouble with the identity column, so I'm no longer in need of this solution. I really apologize.

Thanks.
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

You can use ROW_NUMBER function.

check out this link.

http://msdn.microsoft.com/en-us/library/ms186734.aspx


- Bhavesh
0
 
gkuhrd2001Commented:
HI,

You can use this SQL
First use
Begin Transaction
Select NewID=MAX(ID) from Test;

Then use this "NewID" into your Insert query
Insert into Test (ID,.....) values (newID,....);
Commit

If you need more clarification, do ask me.
Regards
Ankit
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Check the max(ID) and alter the identity count from the count onwards... this will not need any change in your SP.
0
 
eviglottiAuthor Commented:
Let me evaluate and see. If the ROW_NUMBER function something that exists in SQL 2005 or just 2008?

And for each of these, how do we handle if multiple users try to insert at the same time, how do we ensure that no duplicate primary keys are assigned in these processes if two people call the stored procedure at the same time?

Thanks.
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

Row_Number is do exists in SQL SERVER 2005.

Regarding multiple user calling procedure same time, you must be having column with current time stamp.
based on that you can use row_number.

more over, you can use transaction.....also

- Bhavesh
0
 
Bhavesh ShahLead AnalysistCommented:
Hi,

no issues.....
You get one way to get incremental count.so its good for you....
We are happy to share our ideas.... =)

Enjoy


- Bhavesh
0
 
Anthony PerkinsCommented:
>>so I am going to turn the identity off. <<
You cannot "turn the identity off".  All you can do is rebuild the table with the column defined without the IDENTITY attribute.

0
 
eviglottiAuthor Commented:
It turns out I didn't need this after all because of something else that was a configuration change in my application.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.