Post reply

Trapping in stored procedures

  • 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

     

     

     

     

  • March 7, 2005 at 6:18 pm

    #545798

    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

  • March 7, 2005 at 10:09 pm

    #545811

    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...

  • March 8, 2005 at 9:34 am

    #545913

    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

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

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