Link to home
Start Free TrialLog in
Avatar of BlakeMcKenna
BlakeMcKennaFlag for United States of America

asked on

SQL Server Error?

I have a stored procedure that I'm trying to compile and I keep getting this error message:

"Implicit conversion from data type datetime to smallint is not allowed. Use the CONVERT function to run this query."

Here is the SP:

ALTER PROCEDURE [dbo].[spAddUpdate_HEADER_Equipment]
	@returnValue			INT=NULL,
	@equipment_ID			INT=NULL,
	@equipmentName_ID		INT=NULL,
	@equipmentType_ID		INT=NULL,
	@department_ID			INT=NULL,
	@calibrationHouse_ID	INT=NULL,
	@extensionDate			DATETIME=NULL,
	@calibrationDate		DATETIME=NULL,
	@nextCalibrationDate	DATETIME=NULL,
	@combinedUncertainty	DECIMAL(18,9)=NULL,
	@coverageFactor			DECIMAL(18,9)=NULL,
	@serialNO				VARCHAR(50)=NULL,
	@description			VARCHAR(250)=NULL,
	@traceabilityNO			VARCHAR(50)=NULL,
	@interval_ID			INT=NULL,
	@active					INT=NULL,
	@accredited				INT=NULL,
	@createdBy_ID			INT=NULL,
	@updatedBy_ID			INT=NULL,
	@approvedBy_ID			INT=NULL
AS
BEGIN

	IF EXISTS (SELECT equipment_ID FROM HEADER_Equipment WHERE equipment_ID = @equipment_ID)
		BEGIN
			UPDATE	HEADER_Equipment
			   SET	interval_ID = @interval_ID
					,department_ID = @department_ID
					,calibrationHouse_ID = @calibrationHouse_ID
					,calibrationDate = @calibrationDate
					,nextCalibrationDate = @nextCalibrationDate
					,extensionDate = @extensionDate
					,[description] = @description
					,traceabilityNO = @traceabilityNO
					,combinedUncertainty = @combinedUncertainty
					,coverageFactor = @coverageFactor
					,serialNO = @serialNO
					,active = @active
					,accredited = @accredited
					,updatedBy_ID = @updatedBy_ID
					,dateUpdated = GETDATE()
					,approvedBy_ID = @approvedBy_ID            --Receiving the Error on this line
			 WHERE	equipment_ID = @equipment_ID
			 
			 SET @returnValue = @equipment_ID
		END
	ELSE
		BEGIN
			INSERT INTO HEADER_Equipment
				(equipmentName_ID
				 ,equipmentType_ID
				 ,department_ID
				 ,calibrationHouse_ID
				 ,calibrationDate
				 ,nextCalibrationDate
				 ,extensionDate
				 ,[description]
				 ,traceabilityNO
				 ,combinedUncertainty
				 ,coverageFactor
				 ,interval_ID
				 ,serialNO
				 ,active
				 ,accredited
				 ,createdBy_ID
				 ,dateCreated
				 ,updatedBy_ID
				 ,dateUpdated
				 ,approvedBy_ID)
			 VALUES
				(@equipmentName_ID
 				 ,@equipmentType_ID
				 ,@department_ID
				 ,@calibrationDate
				 ,@nextCalibrationDate
				 ,@extensionDate
				 ,@calibrationHouse_ID
				 ,@description
				 ,@traceabilityNO
				 ,@combinedUncertainty
				 ,@coverageFactor
				 ,@interval_ID
				 ,@serialNO
				 ,@active
				 ,@accredited
				 ,@createdBy_ID
				 ,GETDATE()
				 ,@updatedBy_ID
				 ,GETDATE()
				 ,@approvedBy_ID)

			SET @returnValue = @@IDENTITY
		END	
	
	RETURN @returnValue
END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BlakeMcKenna

ASKER

Wow...that was it. Was deceiving in it's location....

Thanks!