Solved

SQL Function to return a value

Posted on 2009-07-05
3
308 Views
Last Modified: 2012-05-07
Hello

Very new to writing sql server 2005 functions.

My first attempt  is a simple function that returns a single value from a table. I am trying

create FUNCTION dbo.GasExpense()
      RETURNS money
AS
BEGIN
    DECLARE @ge MONEY    
    SET @ge = SELECT BudgetGasExpense FROM dbo.ClientInformation WHERE ClientApplicationID = 11620 AND RecordType ='F'
      RETURN(@ge)
END

I know I am going haywire here
SET @ge = SELECT BudgetGasExpense FROM dbo.ClientInformation WHERE ClientApplicationID = 11620 AND RecordType ='f'

But not sure what to do. I need to get @ge the value from that record.

Eventually I will have mopre values to retrieve
SET @we = SELECT WaterExpense FROM dbo.ClientInformation WHERE ClientApplicationID = 11620 AND RecordType ='f'

@total = @ge + @we

return @total

Know this wil be straight foreward once i get the idea down, But cant seem to find answer now.

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 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 24782420
create FUNCTION dbo.GasExpense()
      RETURNS money
AS
BEGIN
    DECLARE @ge MONEY    
    SELECT @ge = BudgetGasExpense FROM dbo.ClientInformation WHERE ClientApplicationID = 11620 AND RecordType ='F'
      RETURN(@ge)
END
0
 
LVL 15

Assisted Solution

by:rob_farley
rob_farley earned 250 total points
ID: 24782439
You should watch out for this. A scalar-valued function like you've described performs notoriously badly (once you're passing parameters in).

You may prefer to use a table-valued function, which will also suit your extended use:

create FUNCTION dbo.Expenses(@clientappid int, @recordtype char(1))
      RETURNS table
AS
return
(
    SELECT BudgetGasExpense, WaterExpense FROM dbo.ClientInformation WHERE ClientApplicationID = @clientappid AND RecordType = @recordtype
)

And you use this like:

select *, (select BudgetGasExpensefrom dbo.Expenses(t.clientapplicationid, 'F'))
from table t

or...

select t.*, e.*
from table t
  cross apply
  dbo.Expenses(t.clientapplicationid, 'F') e

And both these will perform much better than using dbo.GasExpense() separately (once you're passing parameters in).

Basically, the query optimizer can do a much better job of simplifying the query down into a normal join, rather than feeling like it needs to make a separate call for each row.

Hope this helps,

Rob
0
 

Author Closing Comment

by:Charles Baldo
ID: 31600016
Thank you both
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 94
sql query help 2 66
Gettg error - Please help Msg 252, Level 16, State 1, Line 1 3 35
SQL Server 2005 running VERY slowly on new hardware 22 55
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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 …

736 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