I am stuck with an error at my stored procedure. I've searched and tried different stuff, any solutions didn't fix it.
I want to check if the salary of employees are between two different values and that they don't cross the minimum or maximum value.
I use a trigger that fires on insert and update and gets the following data and uses that data to execute the stored procedure.
1 - job_id (INT datatype)
1000.00 - salaris (MONEY datatype)
13 - employee_id (INT datatype)
3000.00 - min_salary (MONEY datatype)
7000.00 - max_salary (MONEY datatype)
Calling of the stored procedure:
EXEC SP_Salary_Check salary, emp_id, job_number, min_salary, max_salary, '' (output)
Parameters of the stored procedure:
(@NEW_SALARY money , @EMP_ID integer, @JOB_NUMBER integer, @MIN_SALARY money, @MAX_SALARY money, @MSG varchar(50) OUTPUT)
If I insert an employee it will break on the parameters of the stored procedure. I will receive this error: Error converting data type nvarcharto money. Which indicated the attribute @NEW_SALARY.
If I change everything to nvarchar it works perfectly. If I only change the datatype money to nvarchar I will receive the same error but with integer instead of money.
Does anyone have a clue on this?
Insert statement:
USE [Outdoor_Paradise]
GO
INSERT INTO [dbo].[employee]
           ([ss_number]
           ,[salary]
           ,[start_date]
           ,[termination_date]
           ,[first_name]
           ,[last_name]
           ,[work_phone]
           ,[extension]
           ,[fax]
           ,[email]
           ,[sales_branch_code]
           ,[job_number]
           ,[manager_id]
           ,[dept_id]
           ,[emp_details_id])
     VALUES
           (12345
           ,1500
           ,'2000-12-12'
           ,NULL
           ,'Foo'
           ,'Bar'
           ,'12345'
           ,'1000'
           ,'fax?'
           ,'[email protected]'
           ,1
           ,1
           ,NULL
           ,NULL
           ,1)
GO