?
Solved

SQL Function to return a value

Posted on 2009-07-05
3
Medium Priority
?
317 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 1000 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 1000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
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…

771 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