Stored Procedure adding new row

NubNub

hello

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.

aNiall8

You can find a sample stored proc for this at http://www.drdev.net/article08.htm

regards,

Niall.

DaleJ

Try this:

Code Snippet

create procedure InsertProc

(

@inputdata varchar(100),

@orptnbr varchar(100) output

)

AS

Begin

DECLARE @wrptnbr varchar(100)

INSERT INTO MyTable VALUES( @inputdata )

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

UPDATE MyTable

SET RptNbr = @wrptnbr

SET @orptnbr = @wrptnbr

End

NubNub

hello

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

So far i have this

CREATE PROCEDURE TestReportNumber1

--@ReportNumber varchar(MAX),

@LocationName varchar(50),

@AINM varchar(15),

@ReportNumberOutput varchar(200) output

AS

BEGIN

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

END

GO

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

DaleJ

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

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.

NubNub

hello

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

NubNub

Stored Procedure adding new row

Re: Stored Procedure adding new row

Re: Stored Procedure adding new row

Re: Stored Procedure adding new row

Re: Stored Procedure adding new row

Re: Stored Procedure adding new row

Re: Stored Procedure adding new row