Suppose if we have formulae's stored in our table row. e.g : for the above table for second row the formulae is 1+2 that means first row's value + second row's. we have to supply the ID field to determine the value and formulae.
Let's make an Stored Procedure. :
Create procedure [dbo].[test_Formulaes1](@ID int) AS begin
declare @formulae_Length varchar(100)
declare @start int
declare @firstDigit varchar(20)
declare @Sign varchar(10)
declare @ThirdDigit varchar(10)
declare @value varchar(10)
declare @finalstring varchar(100)
set @start =1
set @formulae_Length = (select formulae from test_formulae where id = @ID)
while @start <= len(@formulae_Length)
BEGIN
set @value = substring(@formulae_Length,@start,1)
if (isnumeric(@value) = 1) -- there it is a number
begin
if(@Sign != '')
begin
set @ThirdDigit = (select value from dbo.test_formulae where id = @value)
set @finalstring = concat(@finalstring,@ThirdDigit)
set @Sign = null
end
else if( @Sign = '' or @Sign is null)
begin
if (@value = '+' or @value = '-' or @value = '/') --if it is a sign
begin
set @Sign = @value
set @finalstring = concat(@finalstring,@sign)
end
else
begin
set @firstDigit = (select value from dbo.test_formulae where id = @value)
if(@finalstring is null)
begin
set @finalstring = @firstDigit
end
else
begin
set @finalstring = concat(@finalstring,@firstdigit)
end
end
end
end
if(isnumeric(@value) = 0) -- for '(' , ')' , '*' because these are not numeric
begin
if(@Sign is not null)
begin
set @Sign = @value
set @finalstring = concat(@finalstring,@sign)
set @Sign = null
end
else
begin
if(@finalstring is not null)
begin
set @finalstring = concat(@finalstring,@value)
end
else
begin
set @finalstring = @value
end
end
end
SET @start = @start + 1
END
--return @finalstring
declare @sql nvarchar(max)
set @sql='select ('+@finalstring+')'
declare @value1 decimal(38,6)
exec sp_executesql @sql, N'@value1 nvarchar(max) output', @value1 output
select @value1
end
No comments:
Post a Comment