problem with syntex in SP at declare statements

I am trying to write a SP that compares two potential investment plans - i believe that I have the logic correct but am having some issues with the syntex

the TVP is as follows
CREATE TYPE [dbo].[bestScenario] AS TABLE(
      [CustID] [char](4) NULL,
      [CustName] [char](3) NULL,
      [NumberOfYears] [int] NULL,
      [DepositType] [char](6) NULL,
      [Rate] [real] NULL,
      [PrincipalAmount] [float] NULL,
      [FinalAmount] [float] NULL
)


The sp code is below

The errors are:
Msg 207, Level 16, State 1, Procedure InvestmentPlans2, Line 14
Invalid column name 'DepositType'.
Msg 207, Level 16, State 1, Procedure InvestmentPlans2, Line 22
Invalid column name 'FinalAmount'.

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

SET @deptype = DepositType

if @deptype = 'saving'

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

--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
if @deptype = 'saving'
BEGIN
	SET @NP = @P + (@P * @N * (@R +.02)) 
	SET @Nplan= SUM(@NP) 
	SET @Cplan = SUM(@P)
		
		
 --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 
if @deptype = '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 
if @deptype = 'Fixed'
BEGIN
	SET @Cplan = @P * (Power (@N,(1 + @R)))
	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

Open in new window

jrb47Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dwkorCommented:
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?

0
jrb47Author Commented:
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

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

What exactly are you trying to accomplish?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

jrb47Author Commented:
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
0
jrb47Author Commented:
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 

Open in new window

0
jrb47Author Commented:
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

Open in new window

0
dwkorCommented:
Remove dot from the end of both selects - SELECT @Cplan = SUM(FinalAmount) from @invest.

IF @Nplan = @Nplan < @Cplan *1.1 - construct is not valid. you cannot assign the variable in the if statement. This produce an error about ELSE too.

The code fragments like below would also raise the errors - you need to add "from @invest" there
SELECT @deptype = DepositType
WHERE DepositType = 'saving'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jrb47Author Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.