stored procedure - identity field

[drStealth]

Everyone,

I have a table with field ID as an identity, and another field Revision (both are PK, both integer). Many other fields follow. The idea is each ID can have multiple revisions. The problem is I don't want a user to have to enter data every time there is a new revision. I would like for a new record to be created on the table with all the data, same ID, but new revision number. I'm trying to get a stored procedure to work where the user gets prompted for the ID and revision number and perform an insert of the remaining data but am having trouble with the set identity command. Any thoughts on how to approach this would be appreciated. Thanks. drStealth

 

[tlbroadbent]

Identity is an automatically updated value and should be unique. You should not use identity for the ID column in this table.

You could write code like the following procedure to update a row with existing data or insert a new row. Note that this is a very simple example. You may want to add code to increment the revision rather than allowing the user to input it. This should give you a starting point.

Create Procedure AddNewRecord
@id int, @revision int=null
As

If Exists (Select * From TableName Where ID=@ID)
--ID exists in table so Insert row with data from latest revision on table
Begin
Insert TableName (ID, Revision, <column list>)
Select Top 1 ID, @revision, <col List>
From TableName Where ID=@id Order By Revision Desc)
End
Else
--New ID so Insert row with only ID and Revision
Begin
Insert TableName (ID, Revision)
Select Top 1 @id, @revision>
From TableName Where ID=@id)
End
Go Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[drStealth]

Terry, thanks for the response, will try it out. drStealth