i am trying to write a stored procedure that add a new row to a table, this part i can do using INSERT.

I then want to access the ID for the row i have just add, which is the primary key for the table and produced by a auto number. I will then use this value combined with a string to produce a report number which i will put back in a field in the row i just added, and i also need to pass this new value out the procedure.

How do i get the id of the new row from within the stored procedure that just created it

How do i add the new value in the new rows approtate field

How do i pass the new report number out the procedure could i use the return statment with the varible that holds the value.

Re: Stored Procedure adding new row


You can find a sample stored proc for this at



Re: Stored Procedure adding new row


Try this:

Code Snippet

create procedure InsertProc


@inputdata varchar(100),

@orptnbr varchar(100) output




DECLARE @wrptnbr varchar(100)

INSERT INTO MyTable VALUES( @inputdata )

SET @wrptnbr = 'REPORT-' + convert(varchar(15), scope_identity())


SET RptNbr = @wrptnbr

SET @orptnbr = @wrptnbr


Re: Stored Procedure adding new row



thanks for your reply i have made some progress but am still having problems

So far i have this


--@ReportNumber varchar(MAX),

@LocationName varchar(50),

@AINM varchar(15),

@ReportNumberOutput varchar(200) output



DECLARE @tempNumber varchar(300)

INSERT INTO AccidentReports (LocationName, AINM)

VALUES(@LocationName, @AINM)

SET @tempNumber = 'REPORT-' + convert(varchar(200), ReportID)

UPDATE AccidentReports

SET ReportNumber = @tempNumber

SET @ReportNumberOutput = @tempNumber



but get this error

Msg 207, Level 16, State 1, Procedure TestReportNumber1, Line 15

Invalid column name 'ReportID'.

ReportID is the PK and and produced by auto number, i have tryed using AccidentReports.ReportID But get the error

Msg 4104, Level 16, State 1, Procedure TestReportNumber1, Line 15

The multi-part identifier "AccidentReports.ReportID" could not be bound.

Any ideas about what is going on here

Re: Stored Procedure adding new row


What is ReportID and where is it defined

You need to reference SCOPE_IDENTITY() to the id number of the row you just inserted.

Code Snippet

SET @tempNumber = 'REPORT-' + convert(varchar(200), SCOPE_IDENTITY())

Re: Stored Procedure adding new row

Umachandar Jayachandran - MS

If the string is a constant for every row then define a computed column or put the expression in a view instead. There is no reason to store the value if it can be computed at runtime. If you need to search on this computed value then you can index using computed column for example.

Re: Stored Procedure adding new row



The ReportID field is a auto number and the Primary key in a table named AccidentReports . The value of ReportID for the new row just added is what i want to add to the 'Report-' text. Then save the combined string back to the same new row in a field called ReportNumber which is a varchar.

In the proper procedure 'Report-' will be a value passed in that is different depending on what type of report is being added.

Many thank for your input so far