C# + SQL question

[thesadguy]

Hi everyone

Not sure if the solution to my problem really pertains to C# or not, but here goes...

I have 2 tables in a SQL Server 2008 database; call them TypeID and TypeData. TypeID's records are unique and auto-incremented. TypeData contains a TypeID column that references to the TypeID table. TypeData can have multiple records with the same TypeID, but the rest of the data can often be different.

Reading from and updating the existing data is no problem.

The problem is that when I need to insert a new Type, I'd need to first insert a new TypeID, and then use that value (an int) to create the data in TypeData.

Is there a way (using SQL syntax or otherwise) to get, from the database, the value that it just inserted?

If it's not clear so far, here is an example:

TypeID's columns: TypeID(int), TypeDescription(nvarchar(256))
TypeData's columns: TypeID(int), ..., MoreData(int)

Inserting into TypeID:
"INSERT INTO TypeID VALUES ("SomeDescription");"

Then the database takes care of incrementing TypeID so that it's unique, but I need to know what that TypeID is. I can't just reference TypeDescription, because it's not necessarily unique.

Any help would be "muy apreciada" (much appreciated)

 

[mr.B]

Hi thesadguy,

you can use select @@identity

 

[sn3rd]

Hi thesadguy,

you can use select @@identity

@@identity will give you the most recently inserted identity in ANY scope. This may not be what you're looking for if you're firing triggers, etc.

If there's a possibility that another insert is firing off before you call the select, you may want to use SCOPE_IDENTITY() instead.

Read more here:
http://msdn.microsoft.com/en-us/library/ms190315.aspx

 

[FarligOpptreden]

@@identity will give you the most recently inserted identity in ANY scope. This may not be what you're looking for if you're firing triggers, etc.

If there's a possibility that another insert is firing off before you call the select, you may want to use SCOPE_IDENTITY() instead.

Read more here:
http://msdn.microsoft.com/en-us/library/ms190315.aspx

*phwew* I was just about to suggest that. NEVER use @@identity, you'll make your life a living hell in enterprise applications!

So the best solution might be to write a small Stored Procedure, like this:

CREATE PROCEDURE sp_TableName_Create
as
/* INSERT STATEMENT GOES HERE */

return scope_identity()
go

 

[Deenem]

OK, I believe today it's my turn to be anal

CREATE PROCEDURE sp_TableName_Create
as
DECLARE @Ident int

BEGIN TRANSACTION

INSERT INTO TypeID (TypeDescription) VALUES ('newvalue')

SELECT @Ident = scope_identity()

INSERT INTO TableData(TypeID, ....., MoreValues ) VALUES (@Ident, ...., MoreValues)

IF @@ERROR = 0
COMMIT TRANSACTION
ELSE
BEGIN
ROLLBACK
SELECT @Ident = <an error code>
END

SELECT @Ident retval;

 

[FarligOpptreden]

Hehehe - I only suggested the idea. I COULD have provided a complete solution... Thanks Mr. Anal!

EDIT: And yes, rather put ALL insert, update and delete statements into transactions. Would make life much easier later on...