Return Identity While Inserting a Relational Record

Many times when dealing with Relational Tables you will need to get Identity returned so you can set a relationship with other table. When Inserting a record in a table that has a primary key you can get by calling the sql function SCOPE_IDENTITY(). If you use SQL statement in MS Visual Studio in DataSet you need to set the ExecuteMode to Scalar to return the Identity.

Example:

INSERT INTO [dbo].[Fakture] ([ID_podjetja], [St_Fakture], [Leto], [Datum]) VALUES (@ID_podjetja, @St_Fakture, @Leto, @Datum);
SELECT ID_fakture FROM dbo.Fakture WHERE (ID_fakture = SCOPE_IDENTITY())