[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 154
  • Last Modified:

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

0
BlakeMcKenna
Asked:
BlakeMcKenna
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
On the insert portion your missing

@calibrationHouse_ID


before @calibrationDate
0
 
BlakeMcKennaAuthor Commented:
Wow...that was it. Was deceiving in it's location....

Thanks!
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now