Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


SQL Function to return a value

Posted on 2009-07-05
Medium Priority
Last Modified: 2012-05-07

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
    DECLARE @ge MONEY    
    SET @ge = SELECT BudgetGasExpense FROM dbo.ClientInformation WHERE ClientApplicationID = 11620 AND RecordType ='F'

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.

Question by:Charles Baldo
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
LVL 75

Accepted Solution

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

Assisted Solution

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
    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


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,


Author Closing Comment

by:Charles Baldo
ID: 31600016
Thank you both

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

There are some very powerful Dynamic 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 di…
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

650 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