2

I am currently tasked with a project on a database whose schema cannot be changed. I need to insert a new row into a table that requires an ID to be unique, but the original creators of the structure did not set this value to autoincrement. To go around this, I have been using code akin to:

(SELECT TOP 1 [ID] from [Table] ORDER BY [ID] DESC) + 1

when giving the value of the ID field, basically having an inner query of sorts. Problem is that a few lines down, I need that ID I just inputted. If I could set a SQLParameter to output for this column, I could get the value it was set to, problem is I'm using SQL, and not a hard value like I do with other SQLParameters. Can't I use SQL in place of just a value?

This is a potential high volume exchange, so I'd rather not do 2 different queries (one to get id, then one to insert).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
will
  • 1,397
  • 5
  • 23
  • 44

4 Answers4

3

You say you cannot change the schema, but can you add an additional table to the project that does an autoincrement column? Then you could use that table to (safely) create your new IDs and return them to your code.

This is similar to how Oracle does IDs, and sometimes vendor applications for sql server that also run on Oracle will use that approach just to help minimize the differences between the two databases.

Update:
Ah, I just spotted your comment to the other answer here. In that case, the only other thing I can think that might work is to put your two statements (insert a new ID, and then read back the new ID) inside a transaction with the SERIALIZABLE isolation level. And that just kinda sucks, because it leaves you open to performance and locking gotchas.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
1

Is it possible for you to create a stored procedure in the database to do this and the return value of the stored procedure will then return the ID that you need?

Burb
  • 61
  • 4
  • nope, can not change/add fields, stored procedures etc :( Can only read/insert data. – will Sep 06 '11 at 21:43
1

I'm a bit confused about where you need to use this ID. If it inside of the same stored proc just use this method:

DECLARE @NewId int
SELECT TOP 1 @NewId =  [ID] + 1 from [Table] ORDER BY [ID] DESC
SELECT @NewId
Abe Miessler
  • 82,532
  • 99
  • 305
  • 486
  • The problem is that the ID he wants could change between this code and the next statement where then inserts the record. – Joel Coehoorn Sep 06 '11 at 21:51
0

You can put more than one SQL statement in a single SqlCommand. So you could easily do something along the lines of what Abe suggested:

DECLARE @NewId int
SELECT TOP 1 @NewId =  [ID] + 1 from [Table] ORDER BY [ID] DESC
INSERT INTO [Table] (ID, ...) VALUES (@NewId, ...)
SELECT @NewId

Then you just call ExecuteScalar on your SqlCommand, and it will do the INSERT and then return the ID it used.

Community
  • 1
  • 1
Joe White
  • 94,807
  • 60
  • 220
  • 330
  • They are still separate statements, though, and not guaranteed to be atomic. – Joel Coehoorn Sep 06 '11 at 21:58
  • True, but that wasn't OP's question. OP's question was how to get the ID value back to use somewhere else. – Joe White Sep 06 '11 at 22:18
  • Is there any way to do that with SQLParameters and setting one to output? – will Sep 07 '11 at 01:09
  • @Joe - Unless the two statements are atomic, your code is not guaranteed to "get the ID value back to use somewhere else". It just doesn't work in every case: it might get a different ID value created from a separate session instead. It – Joel Coehoorn Sep 07 '11 at 01:34
  • I'm using their database connection class too, which returns a System.data.datatable. When I do the code above, it inserts the row, but it doesn't return newID, is there another way to return it over 'Select @NewId' ? – will Sep 07 '11 at 01:54
  • @Joel, how on earth would the SELECT return a different session's variable? The worst you could get is a key violation, if two sessions both calculated the same ID and then both tried to insert to it -- and the loser would never `SELECT @NewId` to return its duplicate to C#. OP isn't complaining about key violations with the current solution, so I assume there's some mitigating factor already that prevents such key violations. – Joe White Sep 07 '11 at 10:57