Solved

SQL - generate a primary key in a stored procedure insert

Posted on 2011-03-22
9
455 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 19

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
 

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now