getting newly inserted value

[cyberdaemon]

Good day,

I want to get the latest value of the newly inserted record in my label..
The value that i want to get is not the primary key.

Here is my code .

            //for saving
            _patientInformation.firstName = txtFname.Text;


            PatientInformationBL _patientBL = new PatientInformationBL();
            if (_patientBL.addPatientInfo(_patientInformation) == true)
            {
                lblMsg.Visible = true;
                lblMsg.Text = "Record has been saved!";
            }

and my stored procedure is here

   @MRN NVARCHAR(50)
  , @FIRSTNAME  NVARCHAR(50)
  , @PATIENT_ID INT OUT

AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO PATIENT_INFORMATION
        (
           MRN
          , FIRSTNAME
        )
    VALUES( 
         [dbo].[Generate_MRN]('00001', '00001')
          , @FIRSTNAME
           )

        SET @PATIENT_ID = SCOPE_IDENTITY()

when i click on the save button the newly inserted mrn will be place on my lblMrn..
i know that there a best way to do it. can you give me or show me how to do it..

thanks..

[LastMitch]

No primary key

You can try this:

CREATE TABLE PATIENT_INFORMATION(
    MRN nvarchar (50) NULL,
    FIRSTNAME nvarchar(50) NULL,
    PATIENT_ID int SCOPE_IDENTITY(1,1) NOT NULL,
) 

SELECT MAX(PATIENT_ID) FROM PATIENT_INFORMATION;
GO
INSERT INTO PATIENT_INFORMATION (MRN,FIRSTNAME);
VALUES ([dbo].[Generate_MRN]('00001','00001'),@FIRSTNAME);
SET @PATIENT_ID = SCOPE_IDENTITY();
GO
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY];
GO
SELECT @@PATIENT_ID AS [@@PATIENT_ID];
GO

[adam_k]

I have never used this and haven't tested it, but you should be able to get MRN in your app with OUTPUT clause:

INSERT INTO PATIENT_INFORMATION
    (
       MRN
      , FIRSTNAME
    )
OUTPUT INSERTED.MRN
VALUES( 
     [dbo].[Generate_MRN]('00001', '00001')
      , @FIRSTNAME
       )

    SET @PATIENT_ID = SCOPE_IDENTITY()

[cyberdaemon]

Thanks for the help..