CODEWITHSUNDEEP
sqlstored-procedures
holyredbeard
holyredbeard

Reputation: 21228

Getting the next latest created ID

I'm working on a stored procedure, see my last question that explains what I want.

I've created a sproc that adds a movie to the database, and runs separate sprocs from within to create for example a cast for the movie and adds a genre to it (stored in a separate table).

When running the sproc "NewMovie" a new movie gets added to the database, and the ID of that movie is used in the two other sprocs that I run after the movie is added. The problem is that after for example the cast is created, @@IDENTITY refers to CastID, but I need to use the movie ID again after this.

Is it some way that I can get the next latest created ID, or how could I accomplish what I want?

The code so far can be found here if someone has the time to look through it.

Thanks in advance!

Upvotes: 0

Views: 58

Answers (1)

sazh
sazh

Reputation: 1832

Out of curiosity, you already have @MovieID in usp_NewMovie - why not just use that as a parameter to usp_AddGenre and usp_AddMovieRole?

Also you might be better off using SCOPE_IDENTITY() instead of @@IDENTITY http://blog.sqlauthority.com/2007/03/25/sql-server-identity-vs-scope_identity-vs-ident_current-retrieve-last-inserted-identity-of-record/

I made some updates to your code here: http://pastebin.com/rhhrGsuB

Upvotes: 1

Related Questions