Solved

SQL - generate a primary key in a stored procedure insert

Posted on 2011-03-22
9
461 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Independent Software Vendors: 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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms sql + top 1 for each customer 3 66
CONVERT date time to a different time zone. 2 72
What Is an Error? 2 30
calculate running total 8 18
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
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 antispam), the admini…

697 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