[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL - generate a primary key in a stored procedure insert

Posted on 2011-03-22
9
Medium Priority
?
474 Views
Last Modified: 2012-06-21
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!
0
Comment
Question by:eviglotti
9 Comments
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35196101
Hi,

You can use ROW_NUMBER function.

check out this link.

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


- Bhavesh
0
 

Expert Comment

by:gkuhrd2001
ID: 35196112
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
 
LVL 20

Expert Comment

by:Rikin Shah
ID: 35196192
Check the max(ID) and alter the identity count from the count onwards... this will not need any change in your SP.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:eviglotti
ID: 35196262
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35196290
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
 

Accepted Solution

by:
eviglotti earned 0 total points
ID: 35196875
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
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 35196889
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35198623
>>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
 

Author Closing Comment

by:eviglotti
ID: 35229995
It turns out I didn't need this after all because of something else that was a configuration change in my application.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

826 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question