Problem passing caolumn data from trigger to sp 1

[bthale]

I have a update trigger that needs to call a stored procedure, passing the value of a column to the stored procedure. The value is of type int, but it the stored procedure gets it as type nvarchar. The error is :

Server: Msg 8114, Level 16, State 4, Procedure moveData, Line 0
Error converting data type nvarchar to int.
The statement has been terminated.

The code for the trigger is:
CREATE TRIGGER [updateHire] ON [TABLE1]
FOR UPDATE
AS
IF UPDATE (hiredate)
EXEC moveData @inID=msg_id


The code for the stored procedure is:

CREATE PROCEDURE moveData @inID int AS
DECLARE @msg_id int, @name char(50), @number char(10)
DECLARE theCursor CURSOR FOR
SELECT * FROM TABLE1 WHERE TABLE1.msg_id =@inID
OPEN theCursor
FETCH NEXT FROM theCursor
INTO @msg_id, @name, @number
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO TABLE2 VALUES (@msg_id, @name, @number)
DELETE FROM TABLE1 WHERE msg_id=@msg_id
FETCH NEXT FROM theCursor
INTO @msg_id, @name, @number
END

CLOSE theCursor
DEALLOCATE theCursor



Any ideas?? The column msg_id is of type int.
Thanks
Bill

 

[tlbroadbent]

Did you post the actual code for the trigger? It appears that the code of the trigger is in error. As posted, I'm surprised that the trigger compiles.

IF UPDATE (hiredate)
EXEC moveData @inID=msg_id

What is msg_id? You should use a variable such as @msg_id. You'll need to load the variable with a value as below.

Select @msg_id=msg_id From inserted Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.