问题描述
setANSI_NULLSONsetQUOTED_IDENTIFIERONgoALTERPROCEDURE[dbo].[TeacherSchedule]@TeacherIdint,@CourseTypenvarchar(200),@Begindatetime,@EnddatetimeASdeclare@KpjsScheduleDateTimedatetime,@KpjsTimeIdint;declare@datetimedatetime,@weeknvarchar(250),@classtimenvarchar(250),@Kpjsnvarchar(250),@KpjsAssistantint,@KpjsCoursenvarchar(250),@KpjsClassnvarchar(250),@SubjectNotenvarchar(250),@hoursdecimal(18,2),@TeacherHoursdecimal(18,2),@LessonPlanIdint,@LessonPlanLengthbigint,@TeachingLogLengthbigint,@XiaoQunvarchar(250);declare@IsSuccessint,@IsSuccessReasonnvarchar(50);declare@RestTimenvarchar(250);declare@HeGeYesOrNonvarchar(250),@HeGeUserNamenvarchar(250);declare@iint;set@i=0;declare@KpjsLessonPlanLength_KpjsScheduleIdbigint;--tableSchedulecreatetable#ts([datetime]datetimenull,[week]nvarchar(250)null,[time]nvarchar(250)null,[TimeId]intnull,[CourseType]nvarchar(250)null,[KpjsAssistant]intnull,[OneToOneStudentNo]intnull,[IsSuccess]intnull,[IsSuccessReason]nvarchar(250)null,[course]nvarchar(250)null,[class]nvarchar(250)null,[XiaoQu]nvarchar(250)null,[subjectnote]nvarchar(250)null,[LessonPlanId]intnull,--Kpjs,Nhw,Ielts,OneToOne[LessonPlanLength]bigintnull,[TeachingLogLength]bigintnull,[CourseType2]nvarchar(250)null,[course2]nvarchar(250)null,[class2]nvarchar(250)null,[subjectnote2]nvarchar(250)null,[CourseType3]nvarchar(250)null,[course3]nvarchar(250)null,[class3]nvarchar(250)null,[subjectnote3]nvarchar(250)null,[CourseType4]nvarchar(250)null,[course4]nvarchar(250)null,[class4]nvarchar(250)null,[subjectnote4]nvarchar(250)null,[hours]decimal(18,2),[TeacherHours]decimal(18,2),[HeGeYesOrNo]nvarchar(250),[HeGeUserName]nvarchar(250),[RestTime]nvarchar(255))if@CourseType='Kpjs'or@CourseType='All'BEGIN--KpjsdeclareDistinctDateTimecursorforselectdistinctKpjsScheduleDateTime,KpjsTimeIdfromKpjsSchedulewhere(TeacherId=@TeacherIdorAssistantId=@TeacherId)andKpjsScheduleDateTime>=@BeginandKpjsScheduleDateTime<=@EndopenDistinctDateTimeFETCHNEXTfromDistinctDateTimeinto@KpjsScheduleDateTime,@KpjsTimeIdwhile@@FETCH_STATUS=0BEGIN--insertinto#ts([datetime],[time],[hours])values(@KpjsScheduleDateTime,(selectt.KpjsTimeBeginHour+':'+t.KpjsTimeBeginMinute+'-'+t.KpjsTimeEndHour+':'+t.KpjsTimeEndMinuteasclasstimefromKpjsTimetwhereKpjsTimeId=@KpjsTimeId),(select(CONVERT(decimal(18,2),t.KpjsTimeEndHour)-CONVERT(decimal(18,2),t.KpjsTimeBeginHour))+convert(decimal(18,2),(convert(decimal(18,2),t.KpjsTimeEndMinute)-convert(decimal(18,2),t.KpjsTimeBeginMinute))/60)fromKpjsTimetwhereKpjsTimeId=@KpjsTimeId));declarekpjsSchedulecursorforselectconvert(nvarchar(10),s.KpjsScheduleDateTime,20)as[datetime],s.KpjsScheduleWeek,t.KpjsTimeBeginHour+':'+t.KpjsTimeBeginMinute+'-'+t.KpjsTimeEndHour+':'+t.KpjsTimeEndMinuteasclasstime,s.KPJS,cases.AssistantIdwhen@TeacherIdthen1else0endasKpjsAssistant,course.KpjsCourse,KpjsClass.KpjsClass+'('+KpjsClass.KpjsClassStudyType+')'asKpjsClass,KpjsClass.XiaoQu,s.SubjectNote+CASEWHENLEN(s.SpeakingTopic)>0THEN'-'+s.SpeakingTopicELSE''END,(CONVERT(decimal(18,2),t.KpjsTimeEndHour)-CONVERT(decimal(18,2),t.KpjsTimeBeginHour))+convert(decimal(18,2),(convert(decimal(18,2),t.KpjsTimeEndMinute)-convert(decimal(18,2),t.KpjsTimeBeginMinute))/60)ashours,s.TeacherHours,s.IsSuccess,s.IsSuccessReason,KpjsLessonPlan.KpjsLessonPlanHeGeOrNoHeGeasHeGeYesOrNo,KpjsLessonPlan.KpjsLessonPlanHegeUserNameasHeGeUserName,KpjsLessonPlan.KpjsLessonPlanIdasLessonPlanId,s.KpjsScheduleIdasLessonPlanLength,---用于到KpjsLessonPlanLength再次查看lessonplan长度DATALENGTH(KpjsLessonPlan.ClassRoom)+DATALENGTH(convert(nvarchar(100),KpjsLessonPlan.StudentNumber))+DATALENGTH(KpjsLessonPlan.SummaryAfterClass)asTeachingLogLength,CASEWHENRestTimeFrom!=''THENRestTimeFrom+'-'+RestTimeTo+'<br/>'ELSE''END+CASEWHENRestTimeFrom2!=''THENRestTimeFrom2+'-'+RestTimeTo2+'<br/>'ELSE''END+CASEWHENRestTimeFrom3!=''THENRestTimeFrom3+'-'+RestTimeTo3ELSE''ENDASRestTimefromKpjsSchedulesinnerjoinKpjsTimetons.KpjsTimeId=t.KpjsTimeIdinnerjoinKpjsCoursecourseons.KpjsCourseId=course.KpjsCourseIdinnerjoinKpjsClassonKpjsClass.KpjsClassId=s.KpjsClassIdleftjoinKpjsLessonPlanonKpjsLessonPlan.KpjsScheduleId=s.KpjsScheduleIdwhere(s.TeacherId=@TeacherIdors.AssistantId=@TeacherId)ands.KpjsScheduleDateTime=@KpjsScheduleDateTimeands.KpjsTimeId=@KpjsTimeId;openkpjsScheduleset@i=0;fetchnextfromkpjsScheduleinto@datetime,@week,@classtime,@Kpjs,@KpjsAssistant,@KpjsCourse,@KpjsClass,@XiaoQu,@SubjectNote,@hours,@TeacherHours,@IsSuccess,@IsSuccessReason,@LessonPlanId,@KpjsLessonPlanLength_KpjsScheduleId,@TeachingLogLength,@RestTime,@HeGeYesOrNo,@HeGeUserName;while@@FETCH_STATUS=0BEGINset@i=@i+1;if@i=1beginEXEC[KpjsLessonPlanLength]@KpjsLessonPlanLength_KpjsScheduleId,@LessonPlanIdOUTPUT,@LessonPlanLengthOUTPUT,@TeachingLogLengthOUTPUT;insertinto#ts([datetime],[week],[time],[TimeId],[CourseType],[KpjsAssistant],[course],[class],[XiaoQu],[subjectnote],[LessonPlanId],[LessonPlanLength],[TeachingLogLength],[hours],[TeacherHours],[IsSuccess],[IsSuccessReason],[RestTime],[HeGeYesOrNo],[HeGeUserName])values(@datetime,@week,@classtime,@KpjsTimeId,@Kpjs,@KpjsAssistant,@KpjsCourse,@KpjsClass,@XiaoQu,@SubjectNote,@LessonPlanId,@LessonPlanLength,@TeachingLogLength,@hours,@TeacherHours,@IsSuccess,@IsSuccessReason,@RestTime,Cast(@HeGeYesOrNoasnvarchar(250)),@HeGeUserName);endelsebeginif@i=2update#tsset[CourseType2]=@Kpjs,[course2]=@KpjsCourse,[class2]=@KpjsClass,[subjectnote2]=@SubjectNotewhere[datetime]=@datetimeand[week]=@weekand[time]=@classtime;if@i=3update#tsset[CourseType3]=@Kpjs,[course3]=@KpjsCourse,[class3]=@KpjsClass,[subjectnote3]=@SubjectNotewhere[datetime]=@datetimeand[week]=@weekand[time]=@classtime;if@i=4update#tsset[CourseType4]=@Kpjs,[course4]=@KpjsCourse,[class4]=@KpjsClass,[subjectnote4]=@SubjectNotewhere[datetime]=@datetimeand[week]=@weekand[time]=@classtime;endfetchnextfromkpjsScheduleinto@datetime,@week,@classtime,@Kpjs,@KpjsAssistant,@KpjsCourse,@KpjsClass,@XiaoQu,@SubjectNote,@hours,@TeacherHours,@IsSuccess,@IsSuccessReason,@LessonPlanId,@KpjsLessonPlanLength_KpjsScheduleId,@TeachingLogLength,@RestTime;end;closekpjsSchedule;deallocatekpjsSchedule;FETCHNEXTfromDistinctDateTimeinto@KpjsScheduleDateTime,@KpjsTimeIdEndCLOSEDistinctDateTimeDEALLOCATEDistinctDateTimeENDselectconvert(nvarchar(10),[datetime],20)as[datetime],[week],[time],[TimeId],[CourseType],[KpjsAssistant],[OneToOneStudentNo],[IsSuccess],[IsSuccessReason],[course],[class],[XiaoQu],[subjectnote],[LessonPlanId],[LessonPlanLength],[TeachingLogLength],[CourseType2],[course2],[class2],[subjectnote2],[CourseType3],[course3],[class3],[subjectnote3],[CourseType4],[course4],[class4],[subjectnote4],[hours],[TeacherHours],[RestTime],[HeGeYesOrNo],[HeGeUserName],@TeacherIdasTeacherIdfrom#tsorderby[datetime],[time];droptable#ts;
解决方案
解决方案二:
提示你的还不够啊,你把中文转成int,能行吗,哪个变量传递的是中文,搜一下,看看哪里对它进行转换了
解决方案三:
可好看了就会立刻就会
解决方案四:
··中文怎么转换成int你想干嘛呀·
解决方案五:
将你原先保存了合格的int类型的列修改成varchar类型