In this blog post, I’ll walk you through a creative SQL Server script that simulates basic arithmetic operations (+, -, *, /) using loops and logic, rather than direct operators. This can be a fun exercise in understanding control flow and function design in T-SQL.
🔧 Custom Function: dbo.Operation
This function takes three parameters:
@type: the operation type (+,-,*,/)@a,@b: the operands
alter function dbo.Operation(
@type char(1),
@a int,
@b int
)
returns int
/*
select dbo.operation('+',5,2)
select dbo.operation('-',5,2)
select dbo.operation('*',5,3)
select dbo.operation('/',10,3)
*/
as
begin
declare @i int
declare @output int
if(@type = '+')
begin
set @i = 0
while(@i < @b)
begin
set @a = @a + 1
set @i = @i + 1
end
return @a
end
else if(@type = '-')
begin
set @i = 0
while(@i < @b)
begin
set @a = @a - 1
set @i = @i + 1
end
return @a
end
else if(@type = '*')
begin
set @i = 0
set @output = 0
while(@i < @b)
begin
set @output = @output + @a
set @i = @i + 1
end
return @output
end
else if(@type = '/')
begin
set @i = 0
while(@a >= @b)
begin
set @a = @a - @b
set @i = @i + 1
end
return @i
end
return 0
end
go
🧮 Expression Evaluation Engine
This part of the script parses and evaluates a mathematical expression like '2*(3+(4*(5-2)))/6'.
Declare @operatorPriority as table(operator char(1),priority int)
insert into @operatorPriority(operator,priority)
values
('*',1),
('/',2),
('-',3),
('+',3)
Declare @input varchar(50) = '2*(3+(4*(5-2)))/6';
Declare @value varchar(5)
Declare @level int = 1
Declare @inputPosition as table(id int, position int, value varchar(5), level int)
Declare @operatorPosition as table(position int, operator char(1),level int,priority int, evaluationOrder int)
Declare @i int = 1;
while @i <= len(@input)
begin
set @value = substring(@input,@i,1)
if(@value = '(') set @level = @level + 1
insert into @inputPosition(position, value, level) values(@i,@value,@level)
if(@value in ('+','-','*','/'))
begin
insert into @operatorPosition(position,operator, level) values(@i,@value,@level)
end
if(@value = ')') set @level = @level - 1
set @i = @i + 1;
end
select * from @inputPosition
update OP
Set
OP.priority = OPrio.priority
From
@operatorPosition OP
inner join @operatorPriority OPrio on OPrio.operator = OP.operator
Update T
Set
T.evaluationOrder = T.RowNum
From
(select *,row_number() over(order by level desc,priority,position) RowNum from @operatorPosition)T
delete from @inputPosition where value in ('(',')')
declare @evaluationOrder int = 1
declare @evaluationCount int
select @evaluationCount = count(*) from @operatorPosition
declare @evaluationPosition int;
declare @evaluationOperator char(1);
select * from @inputPosition
select * from @operatorPosition order by evaluationOrder
while(@evaluationOrder <= @evaluationCount)
begin
select
@evaluationPosition = position,
@evaluationOperator = operator
from
@operatorPosition where evaluationOrder = @evaluationOrder
update @inputPosition set
value = dbo.Operation(@evaluationOperator,(select top 1 cast(value as int) from @inputPosition where position < @evaluationPosition order by position desc),(select top 1 cast(value as int) from @inputPosition where position > @evaluationPosition order by position))
where
position = @evaluationPosition
delete from t from (select top 1 * from @inputPosition where position < @evaluationPosition order by position desc)t
delete from t from (select top 1 * from @inputPosition where position > @evaluationPosition order by position)t
set @evaluationOrder += 1
select * from @inputPosition
end
No comments:
Post a Comment