July 9th, 2004, 03:43 AM
|
|
Registered User
|
|
Join Date: Jun 2004
Posts: 6
Time spent in forums: < 1 sec
Reputation Power: 0
|
|
|
Cannot get OUTPUT from SP :o(
Hi,
I am new to T-SQL and would like some help with this stored procedure.
Basically, I am trying to check a table to see if a record already exists based on 2 parametres. If the record exists I would like to just output the ID from that record. If the record does not exist I would like to insert a new record outputting the ID.
The code seems to work except it does not output the ID when a new record has been created.
Can anyone see the problem?
Code:
CREATE PROCEDURE dbo.prInsertAssmtAttempt
(
@AssmtInstID int,
@AttemptNo int,
@LastUpdateBy int,
@AuditTypeID int,
@AuditIP varchar(16),
@AssmtAttemptID int OUTPUT
)
AS
DECLARE @Exist int
SELECT *
FROM tblAssmtAttempt
WHERE tblAssmtAttempt.AssmtInstID = @AssmtInstID
AND tblAssmtAttempt.AttemptNo = @AttemptNo
SET @Exist = @@ROWCOUNT
IF @Exist = 0
BEGIN
SELECT @AssmtAttemptID = ISNULL(MAX(AssmtAttemptID),0) + 1 FROM tblAssmtAttempt
INSERT INTO tblAssmtAttempt (AssmtAttemptID, AssmtInstID, AttemptNo, CreatedDate, LastUpdateBy, LastUpdated, AuditVersion, AuditCurrent, AuditTypeID, AuditIP)
VALUES (@AssmtAttemptID, @AssmtInstID, @AttemptNo, GetDate(), @LastUpdateBy, GetDate(), 1, 1, @AuditTypeID, @AuditIP)
END
SELECT @AssmtAttemptID = AssmtAttemptID
FROM tblAssmtAttempt
WHERE tblAssmtAttempt.AssmtInstID = @AssmtInstID
AND tblAssmtAttempt.AttemptNo = @AttemptNo
GO
Any help is much appreciated!
Thanks, Chezza
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
