Solved

SQL Function to return a value

Posted on 2009-07-05
3
271 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:charlesbaldo
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 14

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:charlesbaldo
ID: 31600016
Thank you both
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now