?
Solved

Error in SQL Function

Posted on 2009-07-06
3
Medium Priority
?
211 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 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