Return ID from insert statement

Question

Possible Duplicate:
Best way to get identity of inserted row?

I have an INSERT statement e.g.

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 the patient_id that is present in PATIENT_PROFILE. Is there any way that the insert statement returns the PATIENT_ID?

score 3 · Accepted Answer · answered Aug 27 '12 at 19:21

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()

Return ID from insert statement

1 Answers1