Get Identity of inserted row

[arkulin]

I am trying to create stored procedure which will insert one row in the first table and identity of inserted row in other two tables. For some reason I am receiving error "Procedure or function expects parameter '@id', which was not supplied.". My code is as follows:

ALTER PROCEDURE InsertProf
    (
    @id int output, 
    @added datetime2, 
    @addedby nvarchar(50),
    @ime nvarchar(50),
    @prezime nvarchar(50),
    @fakultet int
    )
AS
BEGIN
SET NOCOUNT ON;

    declare @IdentityOutput table(ID int)

    INSERT INTO np_Profesori
                         (Ime, Prezime)
output inserted.IDProfesor into @IdentityOutput
VALUES        (@ime, @prezime)

SET @id=(select ID from @IdentityOutput)

INSERT INTO np_GdjeRadi
                         (IDProfesor, IDFakultet)
VALUES        (@id, @fakultet)

    INSERT INTO np_DodavanjeProfesora
                         (IDProfesor, Added, AddedBy)
VALUES        (@id, @added, @addedby)

END 

Thank you!

[deceptikon]

You can get the most recent identity with scope_identity():

begin
    insert into np_Profesori (Ime, Prezime) values (@ime, @prezime);

    select @id = scope_identity();

    insert into npGdjeRadi (IDProfesor, IDFakultet) values (@id, @fakultet);
    insert into np_DodavanjeProfesora (IDProfesor, Added, AddedBy) values (@id, @added, @addedby);
end

[arkulin]

@deceptikon, thank you for your reply! For some reason I'm still receiving error "Procedure or function 'InsertProf' expects parameter '@id', which was not supplied" but I can't find where is the mistake.

[deceptikon]

How are you calling the stored procedure?

[arkulin]

Below is my code:

public static string InsertProf(string ime, string prezime, int fakultetId, string adminName) 
try
        {
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Test"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("InsertProf", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@fakultet", fakultetId);
                cmd.Parameters.AddWithValue("@added", DateTime.Now);
                cmd.Parameters.AddWithValue("@addedby", adminName);
                cmd.Parameters.AddWithValue("@ime", ime);
                cmd.Parameters.AddWithValue("@prezime", prezime);
                conn.Open();
                cmd.ExecuteNonQuery();
            }

            return "Inserted..";
        }
        catch 
        {
            return "Error..";
        }

Thank you!

[deceptikon]

You didn't add your output parameter.

[arkulin]

It works now! Thanks a lot!