sql - 调用存储过程错误: Error converting data type nvarchar to datetime

Question

我将过程存储为:

USE [TEAMS-PP]
GO
/****** Object:  StoredProcedure [dbo].[PP_Sp_ObservationSchedule]    Script Date: 06/18/2014 08:32:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  StoredProcedure [dbo].[usp_Organization_AddEdit]    Script Date: 06/16/2014 14:00:49 ******/


ALTER procedure [dbo].[PP_Sp_ObservationSchedule]
(
 @EH_PP_TE_TeacherAcctID uniqueidentifier,    
 @EH_PP_TESRT_TeacherEvalStatusID int,   

 --teacherObsevation
 @EH_PP_TOT_Announced int,
 @EH_PP_ObserverAcctID  uniqueidentifier,
 @EH_PP_TOSRT_TeacherObservationStatusID int,

 --teacherobservationSchedule
 @EH_PP_TOS_ScheduledObservationDateTime datetime,
 @EH_PP_TOS_ObservationRoom varchar(500),
 @EH_PP_ObservingTeacherCourseIDEH uniqueidentifier=null
)
as
begin
BEGIN TRY
print '1'
    BEGIN TRANSACTION 
    declare @EH_PP_TOT_ObservationNum as int
 declare @EH_PP_ObservationID as int
 declare @EH_PP_TeacherAcctId as uniqueidentifier
 declare @EH_PP_TOS_ObserverAcctID as uniqueidentifier
 declare @EH_PP_TOS_TeacherAcctID as uniqueidentifier
 declare @EH_PP_TE_TeacherEvalProcessStartDate as datetime
 set @EH_PP_TOS_ObserverAcctID = @EH_PP_ObserverAcctID
 set @EH_PP_TeacherAcctId = @EH_PP_TE_TeacherAcctID 
 set @EH_PP_TOS_TeacherAcctID = @EH_PP_TE_TeacherAcctID

 -- updating values teacherEvalutions     
     update EH_PP_TeacherEvaluations
     set EH_PP_TE_TeacherEvalProcessStartDate= GETDATE(),
     EH_PP_TESRT_TeacherEvalStatusID =@EH_PP_TESRT_TeacherEvalStatusID
     where EH_PP_TE_TeacherAcctID =@EH_PP_TE_TeacherAcctID

       -- inserting values teacherObsevation 
   set @EH_PP_TOT_ObservationNum =(select count(EH_PP_ObservationID)+1 from dbo.EH_PP_TeacherObservations where EH_PP_TeacherAcctId= @EH_PP_TeacherAcctId)  
  insert into EH_PP_TeacherObservations (EH_PP_TE_TeacherEvalID,EH_PP_TOT_ObservationStartDateTime,EH_PP_TOT_Announced,EH_PP_TOT_ObservationNum,EH_PP_TeacherAcctId,EH_PP_ObserverAcctID,EH_PP_TOSRT_TeacherObservationStatusID)
  values((select EH_PP_TE_TeacherEvalID from EH_PP_TeacherEvaluations where EH_PP_TE_TeacherAcctID =@EH_PP_TE_TeacherAcctID),GETDATE(),@EH_PP_TOT_Announced,@EH_PP_TOT_ObservationNum,@EH_PP_TeacherAcctId,@EH_PP_ObserverAcctID,@EH_PP_TOSRT_TeacherObservationStatusID)

 -- getting unique PK for teacher observation
 select EH_PP_ObservationID = SCOPE_IDENTITY();

 if(@EH_PP_ObservationID!=null)  
 Begin
 -- inserting values in teacherObsevation Schedule
  insert into EH_PP_TeacherObservationSchedule (EH_PP_ObservationID,EH_PP_TOS_ScheduledObservationDateTime,EH_PP_TOS_ObservationRoom,EH_PP_TOS_ObserverAcctID,EH_PP_TOS_TeacherAcctID,EH_PP_ObservingTeacherCourseIDEH)
  values(@EH_PP_ObservationID,@EH_PP_TOS_ScheduledObservationDateTime,@EH_PP_TOS_ObservationRoom,@EH_PP_ObserverAcctID,@EH_PP_TeacherAcctId,@EH_PP_ObservingTeacherCourseIDEH)
 End

 COMMIT
 select 1;
END TRY
BEGIN CATCH
 IF @@TRANCOUNT > 0
 begin
   ROLLBACK
 end
 --SELECT 0;
 SELECT 'EXCEPTION in PP_Sp_ObservationSchedule - '+ERROR_MESSAGE();
 -- +'\n\r  Input Data: \n\r ( evaluation_id-'+cast(@evaluation_id as varchar (100))+',\n\r goal-'+@goal+' ,\n\r createdBy_user_guid='+@createdBy_user_guid+')'  AS result
END CATCH
end

我将此过程称为:

PP_Sp_ObservationSchedule'F5B42B97-4828-4D0B-8129-0FAA37800ADB',1,1,'F5B42B97-4828-4D0B-8129-0FAA37800ADB',1,GETDATE,'abc',''

我收到如下错误:

Msg 8114, Level 16, State 5, Procedure PP_Sp_ObservationSchedule, Line 0
Error converting data type nvarchar to datetime.

我不明白这个错误是否与我执行存储过程的方式有关

或者编译错误本身在存储过程中？？？(存储过程建立成功)。

我怎样才能克服这个问题？？？

Answer 1

您不能将函数作为存储过程参数传递。使用变量存储函数返回值，然后你可以将变量作为参数传递给 SP，例如:

DECLARE @currentdate DATETIME
SET @currentdate = GETDATE()

EXEC PP_Sp_ObservationSchedule'F5B42B97-4828-4D0B-8129-0FAA37800ADB',1,1,'F5B42B97-4828-4D0B-8129-0FAA37800ADB',1,@currentdate,'abc',''
                                                                                       Notice usage of the variable^