SP syntax

Question

SP syntax

smknox

Hall of Fame

Points: 3275
More actions
March 30, 2005 at 3:33 pm

#60245

On the following excerpt from a SP, I am trying to figure out why I can't get a value returned for @intAppointmentID.
When new records are added, @intAilmentID is coming through fine. On the line that requests print of @intAilmentID and @intAppointmentID, I get a value for AilmentId, nothing (blank) for AppointmentID. I've confirmed that a new record is being generated in the Appointment table.
I've been debugging this for days using the debug capacity of QA. It shows a new AppointmentID generated.
???? what more can I look at? Thanks so much.
CREATE PROCEDURE usp_frmSentinelNode_Insert
@PatientID int,
@EmpName varchar(150)
AS
DECLARE @nvAppointmentID nvarchar(20)
DECLARE @nvAilmentID nvarchar(20)
DECLARE @nvTmpAilmentID nvarchar(20)
DECLARE @intAppointmentID int
DECLARE @intAilmentID int
SELECT @nvAilmentID = AilmentID FROM OccMain WHERE PatientID = @PatientID
IF @@ROWCOUNT = 0
BEGIN
  INSERT OccMain
  (
     PatientID
   , EmpCreated
   , EmpUpdated
&nbsp
  VALUES
  (
     @PatientID
   , @EmpName
   , @EmpName
&nbsp
  SET @intAilmentID = @@IDENTITY
END
ELSE
SET @intAilmentID = @nvAilmentID
SELECT @nvAppointmentID = a.AppointmentID, @nvTmpAilmentID = a.AilmentID
FROM Appointment a
INNER JOIN SentinelNodeSt s on a.AppointmentID = s.AppointmentID
WHERE a.RecordID = @PatientID
ORDER BY ApptDate DESC
IF @@ROWCOUNT = 0
BEGIN
  INSERT Appointment
  (
     RecordID
   , AilmentID
   , EmpCreated
   , EmpUpdated
   , ApptTypeID
   , ApptCodeID
&nbsp
  VALUES
  (
     @PatientID
   , @intAilmentID
   , @EmpName
   , @EmpName
   , 2
   , 148
&nbsp
  SET @intAppointmentID = @@IDENTITY
END
ELSE
BEGIN
  SET @intAppointmentID = @nvAppointmentID
PRINT @intAilmentID
PRINT @intAppointmentID
SELECT SentNodeID FROM SentinelNodeST WHERE AppointmentID = @intAppointmentID

Viewing 3 posts - 1 through 2 (of 2 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Are there any triggers defined on table 'Appointment' and are you aware of how that impacts @@IDENTITY and SCOPE_IDENTITY() ifthis is the case ?

Have you tried using SELECT instead of SET, to get the value into your local variable ?

smknox Hall of Fame Points: 3275 More actions · Answer 2

I just can't believe it! Yes, SCOPE_IDENTITY was the issue. THere is a trigger on the Appointment table as you suspected. I wish I had put this out there two weeks ago, I would have considerably more sanity today if I had.

I have to hand it to you guys. I don't know how you do it. This is enough to drive you to ..... something drastic.

Thanks,

Sam