I have a calculation, from which I get either ('/') or ('*'). This is saved in the variable "@op". The problem was, that I was not able to do some calculations with that, because of casting problems, so I casted the values together with my value from "@op". The result of that has the data type NVARCHAR.
The problem is, that I do not get the correct result?! Maybe the casting is not working...
And in general it was not possible for me to generate a decimal value from variable "@Factor", which has the data type NVARCHAR. Here you can see the cast command I tried:
SET @Factor = (SELECT(CAST(@Factor as Decimal(18,6))))
Because that was not working, maybe because of the dot, I tried to get the part before the dot and after it, to cast it in a different way, but unfortunately the wrong result comes out...
I am using SQL Server Managing Studio v17.7
DECLARE @Factor NVARCHAR(20)
DECLARE @op NVARCHAR(20)
DECLARE @num3 FLOAT
SET @num3 = 1.0
SET @op = (SELECT dbo.CalculateFactor1())
SET @Factor = 'SELECT ' + CAST(@num3 AS nvarchar(20)) + @op + CAST(RAND() AS nvarchar(20))
EXEC sp_executesql @Factor -- random factor
DECLARE @test0 NVARCHAR(20)
DECLARE @test1 NVARCHAR(20)
DECLARE @test2 NVARCHAR(20)
DECLARE @test3 NVARCHAR(20)
DECLARE @test4 NVARCHAR(20)
SET @test1 = '0.123456'
SET @test2 = LEFT(@test1, CHARINDEX('.', @test1) - 1) -- gives me the part in front of the comma
SELECT @test2
SET @test3 = SUBSTRING(@test1,(CHARINDEX('.',@test1)+1),7) -- gives me the part after the comma
SELECT @test3
SET @test4 = LEFT(@test1, CHARINDEX('.', @test1) - 1) + '.' + SUBSTRING(@test1,(CHARINDEX('.',@test1)+1),6) -- gives me the complete number
SELECT @test4
SET @test0 = (SELECT(CAST(LEFT(@test1, CHARINDEX('.', @test1) - 1) + '.' + SUBSTRING(@test1,(CHARINDEX('.',@test1)+1),6) AS DECIMAL(18,6))))
SELECT @test0
DECLARE @solution DECIMAL
SET @solution = @test0 * 200.0
SELECT @solution
-- result is 20
-- expected result 24.6912
Need to be explicit
DECLARE @solution DECIMAL(18,6)
SET @solution = cast(@test0 as decimal(18,6)) * 200.0
SELECT @solution