Trapping in stored procedures

Question

Trapping in stored procedures

aries-203501

SSC Journeyman

Points: 79
More actions
March 7, 2005 at 5:44 pm

#86994

Hi Everyone,
Hope somebody can answer my inquiry... I'm trying to use a stored procedure wherein it has a cursor that fetch employee id from a certain table. I'll just execute the stored proc from the SQL analyzer to have those employee id transfered to certain tables. My problem would be handling those employee id that are found in my cursor and does not have any employee id to update to those certain tables. If the script does not have any employee id to update, I would want it to be inserted to another table so I would know. Here is the sample of the code
DECLARE @vOLD_EMPLID, @vNEW_EMPLID AS VARCHAR(11)
DECLARE UpdateEmplID CURSOR FOR
SELECT emplid AS 'OLD EMPLID'
, SUBSTRING(emplid,1,8) AS 'NEW EMPLID'
FROM EmployeeMaster
OPEN UpdateEmplID
FETCH NEXT FROM UpdateEmplID
INTO @vOLD_EMPLID, @vNEW_EMPLID
WHILE @@FETCH_STATUS = 0
UPDATE NewEmployeeMaster SET emplid_old = @vNEW_EMPLID WHERE emplid = @vOLD_EMPLID

FETCH NEXT FROM UpdateEmplID
INTO @vOLD_EMPLID, @vNEW_EMPLID

CLOSE UpdateEmplID
DEALLOCATE UpdateEmplID

Any help will do,

Thanks

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

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

philcart SSC-Forever Points: 47795 More actions · Answer 1

Why use a cursor? Can't this be simple UPDATE and INSERT statements?

INSERT INTO NewEmployeeMaster 
SELECT old.emplid AS 'OLD EMPLID'
  , old.SUBSTRING(emplid,1,8) AS 'NEW EMPLID' 
FROM EmployeeMaster old
  LEFT JOIN NewEmployeeMaster new
  ON new.[OLD EMPLID] = old.emplid
WHERE old.emplid IS NULL

UPDATE New
SET new.emplid_old = old.
FROM NewEmployeeMaster new
  INNER JOIN EmployeeMaster old
  ON new.emplid = old.emplid

I might have got your fields around the wrong way, but you should get the idea.

--------------------
Colt 45 - the original point and click interface

aries-203501 SSC Journeyman Points: 79 More actions · Answer 2

Hi Phillcart,

Thanks for the reply... I'm using cursor since there will be other tables who will be using the same pool of data taken from the cursor... Anyways I found a solution to my problems...

Thanks for the help...

Ram-200771 SSC Rookie Points: 41 More actions · Answer 3

use IF EXISTS condition befor the update statement inside the cursor

IF EXISTS(Employeeid in the updating table)

THEN

UPDATE

ELSE

INSERT in to some other table