SPROC - Error converting data type nvarchar to numeric.

[mortonsa]

Hi All,
I have a procedure called INS_TDIARY. This procedure inserts tasks into a table called TDIARY. If a task needs to be performed multiple times then it will call a stored procedure called GENERATERECURRINGTASKSTEST.

Each of these procedures works beautifully alone, however when I try to have INS_TDIARY call GENERATERECURRINGTASKSTEST I get the following error message:

"Error: Error converting data type nvarchar to numeric., Batch 1 Line 0".

The parameters that INS_TDIARY accepts are:
@DIARYIDY numeric(28) output,
@p_CASEIDY numeric(28),
@p_TASKIDY numeric(28),
@OUTCOMEIDY numeric(28) = null,
@p_ASSIGNEDTOIDY numeric(28),
@p_PRIORITY numeric(28) = null,
@p_DATEDUE datetime,
@DATECLOSED datetime = null,
@QUANTITY numeric(12,2) = null,
@p_TASKACTION varchar(255) = null,
@TASKRESULT varchar(255) = null,
@RECURRINGIND char(1) = 'N',
@RECURRENCEPERIOD char(1) = NULL,
@RECURRENCEDAYS numeric(4) = NULL,
@RECURRENCETIMES numeric(4) = NULL,
@RECURRENCEREGENDAYS numeric(4) = NULL,
@RECURRENCEENDDATE datetime = NULL,
@VOIDREASON varchar(50) = NULL,
@p_USERNAME varchar(50) = null

This is the syntax I am using within INS_TDIARY to call the other procedure:

Exec @GRT = GENERATERECURRINGTASKSTEST
@CASEIDY = @p_CASEIDY,
@TASKIDY = @p_TASKIDY,
@ASSIGNEDTOIDY = @p_ASSIGNEDTOIDY,
@PRIORITY = @p_PRIORITY,
@DATEDUE = @p_DATEDUE,
@TASKACTION = @p_TASKACTION,
@USERNAME = @p_USERNAME,
@RECURPERIOD = v_recurperiod,
@RECURDAYS = v_recurdays,
@RECURLENGTH = v_recurlength,
@RECURENDDATE = v_recurenddate,
@returnvalue = @MyIdent OUTPUT

The parameters that the called procedure is looking for are as follows:

CREATE PROCEDURE dbo.GENERATERECURRINGTASKSTEST
@CASEIDY numeric(28),
@TASKIDY numeric(28),
@ASSIGNEDTOIDY numeric(28),
@PRIORITY numeric(28) = null,
@DATEDUE datetime,
@TASKACTION varchar(255) = null,
@USERNAME varchar(50) = null,
@recurperiod char(1) = null,
@recurdays numeric(4) = null,
@recurlength numeric(4) = null,
@recurenddate datetime = null,
@returnvalue numeric(28) output

It appears as if INS_TDIARY is having trouble receiving the return value from GENERATERECURRINGTASKSTEST but I don't understand why.

If anybody could take a look at this I would really appreciate it! I can also provide more code if that would be helpful.

Thanks,
Stephany

 

[Ruairi]

Try this format. There may be nothing wrong with putting the name of the other proc's variables and the = in there, but you definitely don't have to.

Exec GENERATERECURRINGTASKSTEST @p_CASEIDY, @p_TASKIDY,
@p_ASSIGNEDTOIDY, @p_PRIORITY, @p_DATEDUE,@p_TASKACTION,
@p_USERNAME, v_recurperiod, v_recurdays, v_recurlength,
v_recurenddate, @MyIdent OUTPUT

I run many SP from other SP's this way. Also, dont you need @ in front of the v_recurperiod etc... variables. That may be whats causing the error. As far as i know you can't define a variable in an SP without @. So it would be:

Exec GENERATERECURRINGTASKSTEST @p_CASEIDY, @p_TASKIDY,
@p_ASSIGNEDTOIDY, @p_PRIORITY, @p_DATEDUE,@p_TASKACTION,
@p_USERNAME, @v_recurperiod, @v_recurdays, @v_recurlength,
@v_recurenddate, @MyIdent OUTPUT

HTH. Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[mortonsa]

Thanks Ruairi!
I needed the @ symbol in front of my variables that started with a v_.