Solved

Error in SQL Function

Posted on 2009-07-06
3
207 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:Charles Baldo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:Charles Baldo
ID: 31600196
Thank You
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL order by with "over" statement and row_number() 11 58
Analysis of table use 7 64
Restrict result set 1 45
Email Notifications for SQL 2005 9 38
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

730 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