I am creating following function, while executing it I get the error-msg in my title. Any idea how to fix? I got trough step by step and it seems that it lies in ' set @Kurs = @Kurs * @step'
ALTER function dbo.KurszielSimulator
(@Kurs decimal(15,5),@Kurs_Bilanz_FX decimal(15,5),@F1_B real,@F1 real,
@P1_1_pAW_1 real,@P1_1_pAW_2 real,@P1_2_pAW real ,@P1_3_pAW real,@P1_4_pAW real,@P1_5_pAW real,@P1_6_pAW real,@P1_7_pAW real,@P1_8_pAW real,@P1_9_pAW real,@P1_10_pAW real,@P1_11_pAW real,@P1_12_pAW real,@P1_13_pAW real,@P1_14_pAW real,@P1_15_pAW real,
@GF1_1_1mi real, @GF1_1_1ma real, ..... @GF1_15_10mi real, @GF1_15_10ma real,
@GFP_1 tinyint,@GFP_2 tinyint,@GFP_3 tinyint,@GFP_4 tinyint,@GFP_5 tinyint,@GFP_6 tinyint,@GFP_7 tinyint,@GFP_8 tinyint,@GFP_9 tinyint,@GFP_10 tinyint) returns decimal(15,5) as
begin
declare @step decimal(15,5),@F1prov real,@P1_1 real,@F1_1 tinyint,@P1_2 real,@F1_2 tinyint,@P1_3 real,@F1_3 tinyint,@P1_4 real,@F1_4 tinyint,@P1_5 real,@F1_5 tinyint,@P1_6 real,@F1_6 tinyint,@P1_7 real,@F1_7 tinyint,@P1_8 real,@F1_8 tinyint,@P1_9 real,@F1_9 tinyint,@P1_10 real,@F1_10 tinyint,@P1_11 real,@F1_11 tinyint,@P1_12 real,@F1_12 tinyint,@P1_13 real,@F1_13 tinyint,@P1_14 real,@F1_14 tinyint,@P1_15 real,@F1_15 tinyint
-- Berechnung Kursziel nach oben
if @F1 < @F1_B
begin
set @step = 1.01
while @F1 < @F1_B
set @Kurs = @Kurs * @step
set @P1_1 = (CASE WHEN @Kurs> 0 AND @P1_1_pAW_1 > 0 AND @P1_1_pAW_2 > 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_pAW_2
WHEN @Kurs> 0 AND @P1_1_pAW_1 < 0 AND @P1_1_pAW_2 > 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_pAW_2
WHEN @Kurs> 0 AND @P1_1_pAW_1> 0 AND @P1_1_pAW_2 < 0 THEN (@Kurs/@P1_1_pAW_1)/@P1_1_pAW_2
WHEN @Kurs> 0 AND @P1_1_pAW_1 < 0 AND @P1_1_pAW_2 < 0 THEN (@Kurs/@P1_1_pAW_1 )/ABS(@P1_1_pAW_2) ELSE NULL END)
set @F1_1 = (CASE WHEN @P1_1 >= @GF1_1_1mi AND @P1_1 < @GF1_1_1ma THEN @GFP_1
WHEN @P1_1 >= @GF1_1_2mi AND @P1_1 < @GF1_1_2ma THEN @GFP_2
WHEN @P1_1 >= @GF1_1_3mi AND @P1_1 < @GF1_1_3ma THEN @GFP_3
WHEN @P1_1 >= @GF1_1_4mi AND @P1_1 < @GF1_1_4ma THEN @GFP_4
WHEN @P1_1 >= @GF1_1_5mi AND @P1_1 < @GF1_1_5ma THEN @GFP_5
WHEN @P1_1 >= @GF1_1_6mi AND @P1_1 < @GF1_1_6ma THEN @GFP_6
WHEN @P1_1 >= @GF1_1_7mi AND @P1_1 < @GF1_1_7ma THEN @GFP_7
WHEN @P1_1 >= @GF1_1_8mi AND @P1_1 < @GF1_1_8ma THEN @GFP_8
WHEN @P1_1 >= @GF1_1_9mi AND @P1_1 < @GF1_1_9ma THEN @GFP_9
--ACHTUNG: BEI NACHFOLGENDEM PARAMETER WURDE 'AND' MIT 'OR' ERSETZT
WHEN @P1_1 >= @GF1_1_10mi OR @P1_1 < @GF1_1_10ma THEN @GFP_10
ELSE NULL END)
set @P1_2 = (CASE WHEN @P1_2_pAW <> 0 THEN @Kurs * @Kurs_Bilanz_FX / @P1_2_pAW ELSE NULL END)
set @F1_2 = (CASE WHEN @P1_2 >= @GF1_2_1mi AND @P1_2 < @GF1_2_1ma THEN @GFP_1
WHEN @P1_2 >= @GF1_2_2mi AND @P1_2 < @GF1_2_2ma THEN @GFP_2
WHEN @P1_2 >= @GF1_2_3mi AND @P1_2 < @GF1_2_3ma THEN @GFP_3
WHEN @P1_2 >= @GF1_2_4mi AND @P1_2 < @GF1_2_4ma THEN @GFP_4
WHEN @P1_2 >= @GF1_2_5mi AND @P1_2 < @GF1_2_5ma THEN @GFP_5
WHEN @P1_2 >= @GF1_2_6mi AND @P1_2 < @GF1_2_6ma THEN @GFP_6
WHEN @P1_2 >= @GF1_2_7mi AND @P1_2 < @GF1_2_7ma THEN @GFP_7
WHEN @P1_2 >= @GF1_2_8mi AND @P1_2 < @GF1_2_8ma THEN @GFP_8
WHEN @P1_2 >= @GF1_2_9mi AND @P1_2 < @GF1_2_9ma THEN @GFP_9
--ACHTUNG: BEI NACHFOLGENDEM PARAMETER WURDE 'AND' MIT 'OR' ERSETZT
WHEN @P1_2 >= @GF1_2_10mi OR @P1_2 < @GF1_2_10ma THEN @GFP_10
ELSE NULL END)
SET @F1 = convert(real,(isnull(@F1_1,0) + isnull(@F1_2,0) + isnull(@F1_3,0) + isnull(@F1_4,0) + isnull(@F1_5,0) + isnull(@F1_6,0) + isnull(@F1_7,0) + isnull(@F1_8,0) + isnull(@F1_9,0) + isnull(@F1_10,0) + isnull(@F1_11,0) + isnull(@F1_12,0) + isnull(@F1_13,0) + isnull(@F1_14,0) + isnull(@F1_15,0)))/
convert(real, (
CASE WHEN @F1_1 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_2 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_3 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_4 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_5 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_6 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_7 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_8 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_9 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_10 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_11 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_12 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_13 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_14 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_15 IS NULL THEN 0 ELSE 1 END +
CASE WHEN @F1_1 IS NULL AND @F1_2 IS NULL AND @F1_3 IS NULL AND @F1_4 IS NULL AND @F1_5 IS NULL AND @F1_6 IS NULL AND @F1_7 IS NULL AND @F1_8 IS NULL AND @F1_9 IS NULL AND @F1_10 IS NULL AND @F1_11 IS NULL AND @F1_12 IS NULL AND @F1_13 IS NULL AND @F1_14 IS NULL AND @F1_15 IS NULL THEN 1 ELSE 0 END))
end
-- Berechnung Kursziel nach unten
-- else if @F1 > @F1_B
-- begin
-- set @step = 0.99
-- while @F1 * @step > @F1_B
-- set @F1 = @F1 * @step
-- end
return @Kurs
end
The execution-code looks like:
ALTER PROCEDURE Kurszieltestsim
AS
SET ROWCOUNT 50
UPDATE dbo.GFHilfstabelleV1
SET Systemkursziel = dbo.KurszielSimulator(dbo.GFHilfstabelleV1.Kurs,dbo.GFHilfstabelleV1.Kurs_Bilanz_FX,dbo.GFHilfstabelleV1.F1_B,dbo.GFHilfstabelleV1.F1,
dbo.GFHilfstabelleV1.P1_1_pAW_1 , dbo.GFHilfstabelleV1.P1_1_pAW_2,dbo.GFHilfstabelleV1.P1_2_pAW,dbo.GFHilfstabelleV1.P1_3_pAW,dbo.GFHilfstabelleV1.P1_4_pAW,dbo.GFHilfstabelleV1.P1_5_pAW,dbo.GFHilfstabelleV1.P1_6_pAW,dbo.GFHilfstabelleV1.P1_7_pAW,dbo.GFHilfstabelleV1.P1_8_pAW,dbo.GFHilfstabelleV1.P1_9_pAW,dbo.GFHilfstabelleV1.P1_10_pAW,dbo.GFHilfstabelleV1.P1_11_pAW,dbo.GFHilfstabelleV1.P1_12_pAW,dbo.GFHilfstabelleV1.P1_13_pAW,dbo.GFHilfstabelleV1.P1_14_pAW,dbo.GFHilfstabelleV1.P1_15_pAW,
dbo.GFBerechnungsbasis.GF1_1_1mi, dbo.GFBerechnungsbasis.GF1_1_1ma, dbo.GFBerechnungsbasis.GF1_1_2mi,
dbo.GFBerechnungsbasis.GF1_1_2ma, ....... dbo.GFBerechnungsbasis.GF1_15_10ma,
dbo.GFBerechnungsbasis.GFP_1,dbo.GFBerechnungsbasis.GFP_2,dbo.GFBerechnungsbasis.GFP_3,dbo.GFBerechnungsbasis.GFP_4,dbo.GFBerechnungsbasis.GFP_5,dbo.GFBerechnungsbasis.GFP_6,dbo.GFBerechnungsbasis.GFP_7,dbo.GFBerechnungsbasis.GFP_8,dbo.GFBerechnungsbasis.GFP_9,dbo.GFBerechnungsbasis.GFP_10)
FROM dbo.GFHilfstabelleV1 CROSS JOIN
dbo.GFBerechnungsbasis
WHERE (dbo.GFBerechnungsbasis.Berechnungsvariante = 1) AND F1 <> 0
http://support.microsoft.com/default.aspx?scid=kb;EN-US;155710