Return identifier from insert statement

Question

Possible duplicate:
Best way to get the ID of an inserted row?

I have an INSERT statement, for example.

INSERT INTO PATIENT_PROFILE(PATIENT_NAME, OTHER_NAME, AGE, SEX, PHONE_NO, MOB_NO, CAT_NAME, ADDRESS) VALUES(@PAT_NAME, @OTHER_NAME, @AGE, @SEX, @PHONE, @MOB, @CAT_NAME, @ADD) 

Now I want to get patient_id, which is present in PATIENT_PROFILE. Is there any way that the insert statement returns PATIENT_ID?

Answer 1

Use Scope_Identity ()

So:

 declare @IdInserted int INSERT INTO PATIENT_PROFILE(PATIENT_NAME, OTHER_NAME, AGE, SEX, PHONE_NO, MOB_NO, CAT_NAME, ADDRESS) VALUES(@PAT_NAME, @OTHER_NAME, @AGE, @SEX, @PHONE, @MOB, @CAT_NAME, @ADD) set @IdInserted = Scope_Identity()