CREATE procedure get_ean ( @as_data varchar(13) output)
AS
begin
declare @i integer,@s1 integer,@s2 integer,@c integer,@type integer
select @type = len(@as_data)
if @type <> 7 and @type <> 12 return
select @s1 = 0
select @s2 = 0
select @i = 1
while @i <= @type
begin
if @i%2 = 1
select @s1 = @s1 + convert(integer,substring(@as_data,@i,1))
else
select @s2 = @s2 + convert(integer,substring(@as_data,@i,1))
select @i = @i + 1
end
if @type = 7
select @c = (10 - ((@s2 + @s1 * 3)%10)%10)
else
select @c = (250 - (@s1 + @s2 * 3))%10
select @as_data = @as_data + convert(varchar,@c)
end
------解决方案--------------------
- SQL code
create PROCEDURE get_ean ( out @as_data varchar(13) ) /* RESULT ( column-name,... ) */BEGINdeclare @i integer;declare @s1 integer;declare @s2 integer;declare @c integer;declare @type integer ;select @type = len(@as_data) ; if @type <> 7 and @type <> 12 then return;end if;select @s1 = 0 ;select @s2 = 0 ;select @i = 1 ;while @i <= @type loopif MOD( @i, 1 ) = 1 thenselect @s1 = @s1 + convert(integer,substring(@as_data,@i,1));elseselect @s2 = @s2 + convert(integer,substring(@as_data,@i,1)) ;end if;select @i = @i + 1 ;end loop;if @type = 7 then select @c = (10 - mod(mod((@s2 + @s1 * 3),10),10));elseselect @c = mod((250 - (@s1 + @s2 * 3)),10);end if;select @as_data = @as_data + convert(varchar(100),@c) ;END