Syntax trouble on DECLARE and SET

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Syntax trouble on DECLARE and SET

Author

Topic

ryan.gillies
Starting Member

27 Posts

Posted - 2012-12-17 : 06:29:34

Hi all

I'm trying to create a query to help with some calculations I'm running on some figures in a table. I've taken the logic from a calc function I previously built in VBA and tried to convert it over to TSQL but I'm having trouble.

The code I'm using is as follows:

CREATE FUNCTION SIMPLEINT (@x DATETIME, @prem MONEY)

RETURNS MONEY
AS
BEGIN

	DECLARE @y DATETIME
	DECLARE @simple MONEY
	DECLARE @rate15 DOUBLE
	DECLARE @rate8 DOUBLE

	SET @y = DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),DATEADD(MONTH, 1, GETDATE()))
	SET @rate15 = 0.15 / 12
	SET @rate8 = 0.08 / 12

	WHILE @x <= @y
	BEGIN
		WHILE @x < '1993-01-04'
		BEGIN
			SET @simple = @simple + (@prem * @rate15)
			SET @x = DATEADD("M", 1, @x)
		END
		WHILE @x >= '1993-01-04'
		BEGIN
			SET @simple = @simple + (@prem * @rate8)
			SET @x = DATEADD("M", 1, @x)
		END
	END

RETURN (@simple)

END

And the error it's returning is this:

Msg 156, Level 15, State 1, Procedure SIMPLEINT, Line 11
Incorrect syntax near the keyword 'DECLARE'.
Msg 156, Level 15, State 1, Procedure SIMPLEINT, Line 13
Incorrect syntax near the keyword 'SET'.
Msg 137, Level 15, State 1, Procedure SIMPLEINT, Line 14
Must declare the scalar variable "@rate15".
Msg 137, Level 15, State 1, Procedure SIMPLEINT, Line 15
Must declare the scalar variable "@rate8".
Msg 137, Level 15, State 2, Procedure SIMPLEINT, Line 21
Must declare the scalar variable "@rate15".
Msg 137, Level 15, State 2, Procedure SIMPLEINT, Line 26
Must declare the scalar variable "@rate8".

Can anyone tell me why my last declare and set statements aren't quite right? Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-17 : 06:41:13

Double?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-17 : 06:44:46

replace Double with float or real

--
Chandu

ryan.gillies
Starting Member

27 Posts

Posted - 2012-12-17 : 06:45:36

Good spot, I don't know how I missed that, must have been a carryover from VBA in access - changed it to Decimal and the function flies like a charm! Thanks!

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-17 : 06:56:51

Once you've written it it's quite difficult to spot.
I had to comment out lines that were failing until I realised what was wrong.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ryan.gillies
Starting Member

27 Posts

Posted - 2012-12-17 : 07:29:11

Seems my WHILE loop wasn't quite structured properly (it never ends) so I tweaked it a bit. It creates without any issue, but when I use the function I just get nulls as a result. Can anyone see any obvious flaws in my structure and/or logic...?

CREATE FUNCTION SIMPLEINT (@x DATETIME, @prem MONEY)

RETURNS MONEY
AS
BEGIN

	DECLARE @y DATETIME
	DECLARE @simple MONEY
	DECLARE @rate15 DECIMAL(18,17)
	DECLARE @rate8 DECIMAL(18,17)

	SET @y = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
	SET @rate15 = 0.15 / 12
	SET @rate8 = 0.08 / 12

	WHILE @x < @y AND @x < '01-APR-1993'
	BEGIN
		SET @simple = @simple + (@prem * @rate15)
		SET @x = DATEADD(MONTH, 1, @x)
	END
	WHILE @x < @y AND @x >= '01-APR-1993'
	BEGIN
		SET @simple = @simple + (@prem * @rate8)
		SET @x = DATEADD(MONTH, 1, @x)
	END

RETURN (@simple)

END

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-12-17 : 07:31:43

@simple is never initialised so it will always be null.

maybe @simple = coalesce(@simple,0) + (@prem * @rate15)
Or set it to 0 at the begining.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ryan.gillies
Starting Member

27 Posts

Posted - 2012-12-17 : 07:34:50

That was it Nigel, thanks.
I placed a 'SET @simple = 0' statement before the WHILE loops and it did the trick.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources