SQL Function issue with If / Else (Syntax)

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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Function issue with If / Else (Syntax)

Author

Topic

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-23 : 10:33:07

Here is the function -

Error I get is = Last Statment within a function must return a statement.

Can anyone help with this? Both my If/Else have a return did I miss something?

CREATE FUNCTION Excel_HomesCount
(
	@ProdDate  DATETIME,
	@Market VARCHAR(20),
	@ZoneNo INT,
	@Year INT,
	@Rep INT,
	@MGR INT
)
RETURNS float
AS
BEGIN


IF @Rep > 0
	RETURN	(
				SELECT Sum(tblhomesquote.Homes) AS SumOfHomes
				FROM (tblhomesquote INNER JOIN tblmailings ON tblhomesquote.ZoneNo = tblmailings.ZoneNo) 
					INNER JOIN tblmailingperiods ON (tblhomesquote.SCID = tblmailingperiods.Market) AND (tblhomesquote.ProductionYear = tblmailingperiods.MPYear) AND (tblmailings.CalendarYear = tblmailingperiods.MPYear)
				WHERE (((tblhomesquote.SCID)=@Market) AND tblmailings.productiondate =@ProdDate
					and tblmailings.productiondate Between [tblmailingperiods].[startdate] And [tblmailingperiods].[enddate]) AND ((tblhomesquote.ZoneNo)=@ZoneNo) 
					AND ((tblhomesquote.ProductionYear)=@Year) AND ((tblhomesquote.AssignedRep)=@rep)
			)
ELSE
	RETURN	(
				SELECT Sum(tblhomesquote.Homes) AS SumOfHomes
				FROM (tblhomesquote INNER JOIN tblmailings ON tblhomesquote.ZoneNo = tblmailings.ZoneNo) 
					INNER JOIN tblmailingperiods ON (tblhomesquote.SCID = tblmailingperiods.Market) AND (tblhomesquote.ProductionYear = tblmailingperiods.MPYear) AND (tblmailings.CalendarYear = tblmailingperiods.MPYear)
				WHERE (((tblhomesquote.SCID)=@Market) AND tblmailings.productiondate =@ProdDate
					and tblmailings.productiondate Between [tblmailingperiods].[startdate] And [tblmailingperiods].[enddate]) AND ((tblhomesquote.ZoneNo)=@ZoneNo) 
					AND ((tblhomesquote.ProductionYear)=@Year) AND ((tblhomesquote.assignedmgr)=@MGR)
			)
END

mxfrail
Yak Posting Veteran

84 Posts

Posted - 2009-01-23 : 10:46:47

Check that.

I resolved this myself by just removing the else statement. I guess SQL has some unfortunate hicups and it was referring to the ELSE as a statement.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-23 : 12:01:08

you dont require if else at all


CREATE FUNCTION Excel_HomesCount
(
	@ProdDate  DATETIME,
	@Market VARCHAR(20),
	@ZoneNo INT,
	@Year INT,
	@Rep INT,
	@MGR INT
)
RETURNS float
AS
BEGIN
	RETURN	(
				SELECT Sum(tblhomesquote.Homes) AS SumOfHomes
				FROM (tblhomesquote INNER JOIN tblmailings ON tblhomesquote.ZoneNo = tblmailings.ZoneNo) 
					INNER JOIN tblmailingperiods ON (tblhomesquote.SCID = tblmailingperiods.Market) AND (tblhomesquote.ProductionYear = tblmailingperiods.MPYear) AND (tblmailings.CalendarYear = tblmailingperiods.MPYear)
				WHERE (((tblhomesquote.SCID)=@Market) AND tblmailings.productiondate =@ProdDate
					and tblmailings.productiondate Between [tblmailingperiods].[startdate] And [tblmailingperiods].[enddate]) AND ((tblhomesquote.ZoneNo)=@ZoneNo) 
					AND ((tblhomesquote.ProductionYear)=@Year) AND ((tblhomesquote.AssignedRep=@rep or (@rep=0 and tblhomesquote.assignedmgr=@MGR))
			)

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources