Solved

Error in SQL Function

Posted on 2009-07-06
3
206 Views
Last Modified: 2012-05-07
Hi Very New to writing functions in SQL Server

I have
alter FUNCTION [dbo].[UtilityExpense](@ClientApplicationID INT, @RecordType varchar(1))
      RETURNS money
AS
BEGIN
    DECLARE @ge MONEY
    DECLARE @we MONEY
    DECLARE @ee MONEY
    DECLARE @TotalUtilities MONEY

    SELECT
      @ge = BudgetGasExpense,
        @we = BudgetWaterExpense,
        @ee = BudgetElectricExpense
    FROM dbo.ClientInformation
    WHERE ClientApplicationID = @ClientApplicationID AND RecordType = @RecordType
   
   
    @TotalUtilities = @ge+@we+@ee  -- error here
   
      RETURN(@TotalUtilities)
END


I get an error  'Incorrect syntax near '@TotalUtilities'

I think it's because the sql statement  needs to be  delimeited. But cant figure out why. I wil be expanding on these calcualtions so I need to get vaues from the table and then performs a series of calculations returning one value

Thanks
chuck
0
Comment
Question by:charlesbaldo
3 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 24785848
alter FUNCTION [dbo].[UtilityExpense](@ClientApplicationID INT, @RecordType varchar(1))
      RETURNS money
AS
BEGIN
    DECLARE @ge MONEY
    DECLARE @we MONEY
    DECLARE @ee MONEY
    DECLARE @TotalUtilities MONEY

    SELECT
      @ge = BudgetGasExpense,
        @we = BudgetWaterExpense,
        @ee = BudgetElectricExpense
    FROM dbo.ClientInformation
    WHERE ClientApplicationID = @ClientApplicationID AND RecordType = @RecordType
   
   
   set  @TotalUtilities = @ge+@we+@ee  -- error here
   
      RETURN(@TotalUtilities)
END


0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24785917
OR you can modify it like this

alter FUNCTION [dbo].[UtilityExpense](@ClientApplicationID INT, @RecordType varchar(1))
      RETURNS money
AS
BEGIN
    DECLARE @ge MONEY
    DECLARE @we MONEY
    DECLARE @ee MONEY
    DECLARE @TotalUtilities MONEY

    SELECT @TotalUtilities =
       ISNULL(BudgetGasExpense,0) +
        ISNULL(BudgetWaterExpense,0)+
         ISNULL(BudgetElectricExpense,0)
    FROM dbo.ClientInformation
    WHERE ClientApplicationID = @ClientApplicationID AND RecordType = @RecordType
   
   
      RETURN(@TotalUtilities)
END
0
 

Author Closing Comment

by:charlesbaldo
ID: 31600196
Thank You
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Compare data between two databases 16 110
How to use TOP 1 in a T-SQL sub-query? 14 48
How can I get this column in my query? 2 50
Query group by data in SQL Server - cursor? 3 47
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question