Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-25 : 12:20:20
|
| Adam writes "I have a function that does some complex calculations.I am having some problems using it in a query as part of a stored procedure.The function has several parameters that I would liketo send to it on every record. I keep getting the error:Server: Msg 170, Level 15, State 1, Procedure JMSP_TEST_JMFN_E_U_Prem, Line 43Line 43: Incorrect syntax near 'PMRC'.The function works with variables to pass it but not field references. I am hoping that I wont have to use a cursor and temp table approach. Is there a way to do what I want without it? I have included both the function and the test stored procedure.Thanks a bunch.****Function****ALTER FUNCTION [dbo].[JMFN_E_U_Prem] (@StartAsOfDate DATETIME, -- Start Date of Range @EndAsOfDate DATETIME, -- End Date of Range @TXNEffDate DATETIME, -- Effective date (transaction) @PolExpDate DATETIME, -- Expiration date @WrittenPremium MONEY) -- Base Amount RETURNS @CALCPREM TABLE (NO_OFDAYS FLOAT DEFAULT 0, -- No Of Days in Range TOTAL_DAYS FLOAT DEFAULT 0, -- Total No of days in range FACTOR FLOAT DEFAULT 0, -- Factor for range EARNEDPREM MONEY DEFAULT 0, -- Earned premium UNEARNEDPREM MONEY DEFAULT 0) -- Unearned premiumASBEGIN -- Variable Declaration DECLARE @BEGINDATE DATETIME -- Begining date to calc no of days DECLARE @ENDDATE DATETIME -- Ending date to calc no of days DECLARE @NOOFDAYS FLOAT -- No of days in premium RANGE DECLARE @TOTALDAYS FLOAT -- No of total days in premium DECLARE @FACTOR FLOAT -- Percentange of Premium DECLARE @UNEARNED MONEY -- Unearned premium DECLARE @EARNED MONEY -- Earned premium -- Calulate actual begining date IF @TXNEffDate = @StartAsOfDate SET @BEGINDATE = @TXNEffDate ELSE IF @TXNEffDate < @StartAsOfDate SET @BEGINDATE = @StartAsOfDate ELSE IF @TXNEffDate > @StartAsOfDate SET @BEGINDATE = @TXNEffDate -- Calulate actual ending date IF @PolExpDate = @EndAsOfDate SET @ENDDATE = @PolExpDate ELSE IF @PolExpDate < @EndAsOfDate SET @ENDDATE = @PolExpDate ELSE IF @PolExpDate > @EndAsOfDate SET @ENDDATE = @EndAsOfDate -- Compute No of days in range and check if less than zero SET @NOOFDAYS = DATEDIFF(DAY,@BEGINDATE,@ENDDATE)+ 1 IF @NOOFDAYS < 0 SET @NOOFDAYS = 0 -- Compute Total no of days and check if less than zero SET @TOTALDAYS = DATEDIFF(DAY,@TXNEffDate,@PolExpDate)+ 1 IF @TOTALDAYS < 0 SET @TOTALDAYS = 0 -- Compute Factor --IF NOT (@NOOFDAYS = 0 OR @TOTALDAYS = 0) -- Check for zero SET @FACTOR = @NOOFDAYS/@TOTALDAYS -- Caluclate Earned Premium IF (@NOOFDAYS = 0 OR @TOTALDAYS = 0) -- Not started yet or no days in date range SET @EARNED = 0 -- You have not earned any premium ELSE IF @FACTOR >= 1 -- End of policy SET @EARNED = @WrittenPremium -- You have earned all the premium ELSE IF @FACTOR < 1 -- A number of days have been earned, calculate SET @EARNED = ROUND(@WrittenPremium * @FACTOR, 2) -- Caluclate Unearned Premium IF (@NOOFDAYS = 0 OR @TOTALDAYS = 0) -- Not started yet or no days in date range SET @UNEARNED = @WrittenPremium -- You have not earned any premium ELSE IF @FACTOR >= 1 -- End of policy SET @UNEARNED = 0 -- You have earned all the premium ELSE IF @FACTOR < 1 -- A number of days have been earned, calculate SET @UNEARNED = ROUND(@WrittenPremium * (1- @FACTOR),2) -- Copy working data to outgoing structure and return INSERT @CALCPREM SELECT @NOOFDAYS, @TOTALDAYS, @FACTOR, @EARNED, @UNEARNED RETURN END*****Test Stored Procedure*****ALTER PROCEDURE [dbo].[JMSP_TEST_JMFN_E_U_Prem] (@STA |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-25 : 12:21:26
|
| This appears to have been chopped off due to a 4,000 character limit in the ASK SQL Team section. You'll need to repost the full procedure directly in the Forum. |
 |
|
|
|
|
|
|
|
Using a