Stored Procedures

Question

Post reply

Stored Procedures

Michaelle

SSC Veteran

Points: 215
More actions
April 21, 2009 at 9:35 pm

#99614

ALTER PROCEDURE dbo.insertproblem
(
@onstationtime SMALLDATETIME,
@flttime Decimal,
@userid NVARCHAR (50),
@subject NVARCHAR (100)
)
AS
INSERT INTO TestingWork
(onstationtime,
flttime,
userID,
subject)
VALUES
(@onstationtime,
@flttime,
@userid,
@subject
)
RETURN SCOPE_IDENTITY()
--DECLARE @reportID NVARCHAR (15)
DECLARE @autoID INT
DECLARE @RETURN_VALUE
SELECT @RETURN_VALUE AS autoID
UPDATE TestingWork SET reportID = 'PMR' + autoID
WHERE autoID = @autoID

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 1

Steve Jones - SSC Editor

SSC Guru

Points: 736270

April 21, 2009 at 10:10 pm

#981932

Do you have a question?

SQL* SSCertifiable Points: 6638 More actions · Answer 2

SQL*

SSCertifiable

Points: 6638

April 21, 2009 at 10:21 pm

#981937

can u provide table schema

🙂

Lowell SSC Guru Points: 323518 More actions · Answer 3

since you are just setting the ReportId to a string plus the autoid, you could make the column a calculated field and be done with it:

Alter Table TestingWork ADD NewReportId AS 'PMR' + convert(varchar(30),autoID)

but your proc is messed up; here's my best guess at a fix:

ALTER PROCEDURE dbo.insertproblem

(

@onstationtime SMALLDATETIME,

@flttime Decimal,

@userid NVARCHAR (50),

@subject NVARCHAR (100)

)

AS

Declare @InsertedID int

INSERT INTO TestingWork

(onstationtime,

flttime,

userID,

subject)

VALUES

(@onstationtime,

@flttime,

@userid,

@subject

)

SELECT @InsertedID = SCOPE_IDENTITY()

/*this code did nothing, so i commented it out

--DECLARE @reportID NVARCHAR (15)

DECLARE @autoID INT

DECLARE @RETURN_VALUE

SELECT @RETURN_VALUE AS autoID

*/

UPDATE TestingWork SET reportID = 'PMR' + convert(varchar(30),@InsertedID)

WHERE autoID = @InsertedID

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Michaelle SSC Veteran Points: 215 More actions · Answer 4

Thank you so much. It worked perfectly.

Thanks a million.