problem with syntex in SP at declare statements

There are the problematic lines:
SET @deptype = DepositType
SET @Cplan = SUM(FinalAmount)

You obviously want to select them from tvp. So second one (finalamount) should be changed to:

SELECT @Cplan = SUM(FinalAmount) from @invest.

As for the first one (DepositType) - how would you like to handle multiple records in TVP? What deposittype do you want to select?

hmm.....what would my options be - this is my first attempt at tvp's and this type of comparison

Can you use a WHERE statement

I can also see that changing these are going to create a few new for which i will open related questions.

thanks

you can definitely use where statement - selects from tvps are like from regular tables.

What exactly are you trying to accomplish?

I have four new plans that I need to test again the plans now to see which ones will give acceptable marketing changes

so if deptype = saving
then i use it to do calculations in new plans 1 and new plan 2

if deptype = fixed then i calcualte something different.

so i am not sure how to code importing the deposit type and setting @dept

SO....changing the code to this gives me these new errors

Msg 156, Level 15, State 1, Procedure InvestmentPlans2, Line 24
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Procedure InvestmentPlans2, Line 48
Incorrect syntax near the keyword 'IF'.

I am wondering if it is because I need an else in there
but that is not what i want
I want it to calculate each of the new plans based on the type of deposit type

CREATE PROC [dbo].[InvestmentPlans2]
(@invest as bestScenario READONLY )
AS
DECLARE @R FLOAT  -- rate of interst
DECLARE @N INT  -- number of years 
DECLARE @P FLOAT  --Prinicipal amount
DECLARE @NP Float --New Principal amount
DECLARE @F Float -- Final Amount 
DECLARE @Cplan FLOAT  -- Current plan final amount 
DECLARE @Nplan FLOAT  -- proposed plan  final amount 
Declare @deptype char(6) -- deposit type
DECLARE @D Float --difference between current plan and new plan

SELECT @deptype = DepositType
WHERE DepositType = 'saving'

-- calculate compound interest for saving customers::plan 1 
BEGIN 	
	SET @NP = @P * (Power (@N,(1 + @R))) 
	SET @Nplan= SUM(@NP)
	SELECT @Cplan = SUM(FinalAmount) from @invest.

--check to see if proposed plan @Nplan is > current @Cplan  
IF @Nplan < @Cplan *1.1
   	BEGIN
	PRINT 'Plan 1 suitable' 
	END 
		ELSE 
		BEGIN
		SET @D = (@Cplan * 1.1)/100 
		PRINT 'Plan 1 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 
		END

END
-- check for saving customers for plan 2
SELECT @deptype = DepositType
WHERE DepositType = 'saving'

BEGIN
	SET @NP = @P + (@P * @N * (@R +.02)) 
	SET @Nplan= SUM(@NP) 
	SELECT @Cplan = SUM(FinalAmount) from @invest.
		
		
 --compare proposed vs  current for > 10% rule  for  plan 2
IF @Nplan < @Cplan *1.1
   	BEGIN
	PRINT 'Plan 2 suitable' 
	END 
		ELSE 
		BEGIN
		SET @D = (@Cplan * 1.1)/100 
		PRINT 'Plan 2 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 
		END
END		
-- to check for Fixed customers 
SELECT @deptype = DepositType
WHERE DepositType = 'fixed'

-- Increase R by 2 %  for fixed for  plan 3 
BEGIN 
	SET @Nplan= @P * @N * (@R +2) 
	SET @Cplan = @P * (Power (@N,(1 + @R)))

 -- compare proposed vs  plan for  plan 3 
IF @Nplan < @Cplan *1.1
   	BEGIN
	PRINT 'Plan 3 suitable' 
	END 
		ELSE 
		BEGIN
		SET @D = (@Cplan * 1.1)/100 
		PRINT 'Plan 3 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 
		END
END	
-- Calculate fixed plans with 5% bonus for plan 4 
SELECT @deptype = DepositType
WHERE DepositType = 'saving'

BEGIN
	SELECT @Cplan = SUM(FinalAmount) from @invest
	set @Nplan= @P * (Power (@N,(1 + @R))) + (.05 *(( @P * (Power(@N,(1 + @R))))* @N))
			 
	
-- compare proposed vs  current for >10%  for  plan 4
IF @Nplan < @Cplan *1.1
   	BEGIN
	PRINT 'Plan 1 suitable' 
	END 
		ELSE 
		BEGIN
		SET @D = (@Cplan * 1.1)/100 
		PRINT 'Plan 1 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 
		END
	
 END 

Select allOpen in new window

new code

NEW errors
Msg 156, Level 15, State 1, Procedure InvestmentPlans2, Line 24
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure InvestmentPlans2, Line 24
Incorrect syntax near '<'.
Msg 156, Level 15, State 1, Procedure InvestmentPlans2, Line 28
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure InvestmentPlans2, Line 48
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure InvestmentPlans2, Line 48
Incorrect syntax near '<'.
Msg 156, Level 15, State 1, Procedure InvestmentPlans2, Line 52
Incorrect syntax near the keyword 'ELSE'.
Msg 156, Level 15, State 1, Procedure InvestmentPlans2, Line 71
Incorrect syntax near the keyword 'IF'.
Msg 102, Level 15, State 1, Procedure InvestmentPlans2, Line 71
Incorrect syntax near '<'.
Msg 156, Level 15, State 1, Procedure InvestmentPlans2, Line 75
Incorrect syntax near the keyword 'ELSE'.

CREATE PROC [dbo].[InvestmentPlans2]
(@invest as bestScenario READONLY )
AS
DECLARE @R FLOAT  -- rate of interst
DECLARE @N INT  -- number of years 
DECLARE @P FLOAT  --Prinicipal amount
DECLARE @NP Float --New Principal amount
DECLARE @F Float -- Final Amount 
DECLARE @Cplan FLOAT  -- Current plan final amount 
DECLARE @Nplan FLOAT  -- proposed plan  final amount 
Declare @deptype char(6) -- deposit type
DECLARE @D Float --difference between current plan and new plan

SELECT @deptype = DepositType
WHERE DepositType = 'saving'

-- calculate compound interest for saving customers::plan 1 
Begin
	SET @NP = @P * (Power (@N,(1 + @R))) 
	SET @Nplan= SUM(@NP)
	SELECT @Cplan = SUM(FinalAmount) from @invest.

--check to see if proposed plan @Nplan is > current @Cplan  
IF @Nplan = @Nplan < @Cplan *1.1
   	BEGIN
	PRINT 'Plan 1 suitable' 
	END 
ELSE 
		BEGIN
		SET @D = (@Cplan * 1.1)/100 
		PRINT 'Plan 1 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 
		END

END
-- check for saving customers for plan 2
SELECT @deptype = DepositType
WHERE DepositType = 'saving'

BEGIN
	SET @NP = @P + (@P * @N * (@R +.02)) 
	SET @Nplan= SUM(@NP) 
	SELECT @Cplan = SUM(FinalAmount) from @invest.
		
		
 --compare proposed vs  current for > 10% rule  for  plan 2
IF @Nplan = @Nplan < @Cplan *1.1
   	BEGIN
	PRINT 'Plan 2 suitable' 
	END 
		ELSE 
		BEGIN
		SET @D = (@Cplan * 1.1)/100 
		PRINT 'Plan 2 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 
		END
END		
-- to check for Fixed customers 
SELECT @deptype = DepositType
WHERE DepositType = 'fixed'

-- Increase R by 2 %  for fixed for  plan 3 
BEGIN 
	SET @NP = @P * @N * (@R +2)
	SET @Nplan= SUM(@NP) 
	SELECT @Cplan = SUM(FinalAmount) from @invest.

 -- compare proposed vs  plan for  plan 3 
IF @Nplan = @Nplan < @Cplan *1.1
   	BEGIN
	PRINT 'Plan 3 suitable' 
	END 
		ELSE 
		BEGIN
		SET @D = (@Cplan * 1.1)/100 
		PRINT 'Plan 3 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 
		END
END	
-- Calculate fixed plans with 5% bonus for plan 4 
SELECT @deptype = DepositType
WHERE DepositType = 'saving'

BEGIN
	SET @NP= @P * (Power (@N,(1 + @R))) + (.05 *(( @P * (Power(@N,(1 + @R))))* @N))
	SET @Nplan= SUM(@NP) 
	SELECT @Cplan = SUM(FinalAmount) from @invest
			 
	
-- compare proposed vs  current for >10%  for  plan 4
IF @Nplan < @Cplan *1.1
   	BEGIN
	PRINT 'Plan 1 suitable' 
	END 
		ELSE 
		BEGIN
		SET @D = (@Cplan * 1.1)/100 
		PRINT 'Plan 1 is not suitable,the difference is'
		PRINT @D
		PRINT '%' 
		END
	
 END

Select allOpen in new window

thankyou so much for helping me debug this!
the syntax is correct but the math is not right so........
i am opening another question for that portion.