e媒網絡

USE [Demo]
GO
/****** Object:  UserDefinedFunction [dbo].[GetTermSortNo]    Script Date: 2023/4/4 14:50:57 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[GetTermSortNo](@TermCode varchar(10),@GradeCode varchar(10))
returns int
as
begin
declare @TermSortNo int
declare @TermIntCode int
declare @GradeIntCode int
declare @DiffIntCode int
declare @DiffStrCode varchar(50)
declare @C varchar(50)
declare @IntC int
declare @i int
declare @count int
set @TermSortNo=0
if @TermCode is null or @GradeCode is null
  return -1
set  @TermIntCode=Convert(int,@TermCode)
set  @GradeIntCode=Convert(int,@GradeCode+@GradeCode+'1')
set @DiffIntCode=@TermIntCode-@GradeIntCode
set @DiffStrCode=CONVERT(varchar(50),@DiffIntCode)
set @Count=len(@DiffStrCode)
set @i=1
while(@i<=@Count)
    begin
    set @C= substring(@DiffStrCode,@i,1)
    set @IntC= Convert(int,@C)
    set @TermSortNo= @TermSortNo+@IntC
    set @i=@i+1
    end
return @TermSortNo
end