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
Solved

SQL Function to return a value

Posted on 2009-07-05
3
297 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 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:charlesbaldo
ID: 31600016
Thank you both
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL help 5 56
execute a MS SQL script as a schedule SQL job 72 138
SQL Server Degrading on Write 13 70
Sql Server group by 10 44
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 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