Calling a user-defined function?

In the below statement where I am calling user-defined function
dbo.udf_calcbill_hotel, is that a legal select statement?  I keep
getting syntax errors and I can't figure out what's wrong.  I'm
attempting to call the function by passing in data from the other table
that the function table is joined to.

If this is wrong, is there another way to do this?  Sorry I can't post
all the table data.



DECLARE @initfee money, @fee money, @billbase int, @folup tinyint
SET @folup = NULL

CREATE TABLE #bill_bldgs
	(
	BldgSec_ID int,
	BldgSec_Units smallint NULL,
	FolUp tinyint NULL
	)

	-- get a table of the billable buildings
	INSERT #bill_bldgs
	SELECT BldgSec_ID, BldgSec_Units, @folup AS FolUp
	FROM dbo.tblBldg_Sec
	WHERE Prop_ID = 2721 AND BldgSec_SectionOfID IS NULL AND BldgSec_Active
= 1 AND dbo.udf_Billable(BldgSec_ID) = 1


SELECT @initfee = MAX(cbh.Flat_Fee), @fee = SUM(cbh.ByRate_Fee),
@billbase = COUNT(*)
FROM #bill_bldgs bb INNER JOIN 
dbo.udf_calcbill_hotel(bb.BldgSec_ID, bb.BldgSec_Units, bb.FolUp) cbh 
ON bb.BldgSec_ID = cbh.BldgSec_ID

DROP TABLE #bill_bldgs

SELECT @initfee AS InitFee, @fee AS ByRateFee


Mia J.

*** Sent via Developersdex http://www.developersdex.com ***
0
Mij
6/11/2010 1:47:24 PM
sqlserver.programming 1873 articles. 0 followers. Follow

2 Replies
591 Views

Similar Articles

[PageSpeed] 49

More information is that I am trying to take a table of Buildings, i.e.

BldgSec_ID  |  BldgSec_Units  | FolUp
1              35                N
2              58                N
3              300               N

and somehow put each row into my function that calculates the billing
fees, so that I get a table like this:

BldgSec_ID  |  Flat_Fee  |  ByRate_Fee
1              435            210
2              435            406
3              435            2400
----------------------------------------
            Max               Sum

so that I can get a max(Flat_Fee) and a SUM(ByRate_Fee) from the table.

Mia J.

*** Sent via Developersdex http://www.developersdex.com ***
0
Mij
6/11/2010 3:57:26 PM
Mij (mdsj@infi.net) writes:
> In the below statement where I am calling user-defined function
> dbo.udf_calcbill_hotel, is that a legal select statement?  I keep
> getting syntax errors and I can't figure out what's wrong.  I'm
> attempting to call the function by passing in data from the other table
> that the function table is joined to.
> 
> If this is wrong, is there another way to do this?  Sorry I can't post
> all the table data.

If you are on SQL 2000, you will probably habe to run a cursor 
and run a query row by row.

On SQL 2005 and later you need to use the CROSS APPLY operator:
 
 SELECT @initfee = MAX(cbh.Flat_Fee), @fee = SUM(cbh.ByRate_Fee),
        @billbase = COUNT(*)
  FROM #bill_bldgs bb 
  CROSS APPLY dbo.udf_calcbill_hotel(bb.BldgSec_ID, 
                      bb.BldgSec_Units, bb.FolUp) cbh 

You should verify that this query gives the desired result.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
6/11/2010 10:20:25 PM
Reply:

Similar Artilces:

Viewing Excel Reports as local user
Hi, I am trying to open up a Excel report that was generated from Dynamics GP10. It gives me the following error: Cannot generate SSPI contect' when trying to refresh. Please advise all steps I need to take to enable me to run this. When I say OK I keep getting a error message when exporting the report from GP 10 to Excel. A window opens prompting for a user name and password. sa works fine. When trying to use a normal user id getting this message Connection Failed SQLState 42000 SQL Server Error 18456 I have been using KB 949524 to troubleshoot other issues. This KB doesn'...

Call in?
Do new owners of Office X have to call up Microsoft to register their copy? If yes, what's the number? thanks. Ming <asdf@asdf.com> wrote: > Do new owners of Office X have to call up Microsoft to register their > copy? If yes, what's the number? thanks. Nope. We're not using Windoze :->>> This version does not require activation. Corentin -- - Mac:MS MVP (Francophone) - (MS) MVP: http://support.microsoft.com/default.aspx?ln=FR&scid=fh;FR;mvp Newsgroups produits MS: http://support.microsoft.com/newsgroups/?ln=FR ...

laptop users are unable to replicate
Outlook 2000 windows 2000 pro When laptop users try and replicate their inbox with our exchange 5.5 server they get an error message saying "Error syncronizing server" [80004005-501-550] etc I have tried all the stuff suggested in previous posts (deleted .ost etc) but this problem seems to be a permissions issue as when I log onto the laptop as an Exchange administrator there is no problem. All laptop owners are NT administrators of the actual laptop can anyone help ? Del We use NAV for Exchange, and I've found that viruses will cause the replication to fail on the pa...

Email reply tracks but icon does not change in Outlook for some users
We have email replies tracking mostly successfully, but we still have one bug. When I receive a reply to a tracked email, the email shows as tracked in my Outlook summary view. However, for other users, the icon does not change. When they open up the email, it still gives them the option to track. But if I go into the contact record they used for Regarding, I can see the email in the history. So it's tracking, but they aren't seeing it as tracked. They have different rights than me in both CRM and in the domain. If they click the Track button, the Regarding will populate and the...

Reconnect multiple users
Hello, I have to reconnect many users from a restore to their respective AD accounts. Is there any way to automate this task with a script so that I don't have to reconnect users one by one. Thanks, Lotfi MBConn.exe -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------------- "Lotfi" <Lotfi@discussions.microsoft.com> wrote in message news:1A489989-47D7-4B44-BD5C-70050A6284CE@microsoft.com... > Hello, > > I have to reconnect many users from a restore to their respective AD > accounts. Is there any way t...

Covered Call Options
Hello all, I do I go about dealing with a covered call transaction in Microsoft Money 2007 Premium? Currently what I am doing is inputing the option symbol, and choosing the transaction "Sell to Open" then when I go to expire the option I try to do a Sell to Close. However it does not seem to work? Also what happens if instead of expired I has the position assigned? how would I account for that? Thank you. Shhhh In microsoft.public.money, Shhhh wrote: > >I do I go about dealing with a covered call transaction in Microsoft >Money 2007 Premium? Currently what I am doing is...

Call center tracking and reporting
Anyone use CRM 4.0 for call tracking? Our Sales/Marketing is set up. Now I need to decide how to most efficiently customize CRM 4.0 for our company's call tracking portion. Can someone send me a link to information? thanks ...

remove Outlook from startup for 'limited' user, but keep it for 'A
As admin user, I want Outlook to open when I startup, but another user on my computer doesn't want Outlook to open upon startup. I'm sure it's a simple fix, but when I've tried to eliminate it from his startup, it won't allow me to...indicating that the file's in use, or write protected,, etc. Any suggestions would be greatly appreciated. Thanks, :-) Lo LoLo <LoLo@discussions.microsoft.com> wrote: > As admin user, I want Outlook to open when I startup, but another > user on my computer doesn't want Outlook to open upon startup. I'm > sure...

MAXA worksheet function
The functions MAXA and MINA appeared when I upgraded to Excel 2003. When I use them in a worksheet & try to save the result, I receive a message saying that these functions cannot be converted to the file format that I am using. The file format that I am using gives Excel 97, 2003, NT, 5.0 and 95. What am I doing wrong? Thanks! IIRC, MINA and MAXA appeared in XL97, so saving to XL5/95 compatible format won't work. Is there any reason you need to save in that format? It makes your workbook twice the size of saving it as an XL workbook (e.g., 97/03 format) since it saves both for...

telephone calls
when placing a telephone call using outlook, everything works fine except I can't hear the person i am talking to. They can hear me find. When I use a telephone program not associated with outlook every thing works great. can someone help ...

Calling Macro
Hi All, I have macro like this sub unique() '''''''' '''''''' Counter_item = 0 For Each Item In nodups counter_item = counter_item + 1 bic_var = Item Call Ps_Match Next Item end sub I have another macro Sub Ps_Match() ''''' ''''' If counter_item <= 1 Then call another_macro() end if end sub In the first macro i am declaring counter_item equals to 1. In my second macro i need to call that number. At the to of the module put: Public Counter_I...

User in need of help.
Could an administrator send an email out from a user account? If not, is there a software that could do it. Someone sent an email thru my account and I am told only someone with my password could have done it. Is it true? All help is appreciated. On Exchange Server an Administrator can change your mailbox rights to allow anybody send emails in your name "sirous" <sirous@discussions.microsoft.com> escribi� en el mensaje news:5A728C6F-DE70-49DF-A716-E4CFDFFB646F@microsoft.com... > Could an administrator send an email out from a user account? > If not, is there a sof...

How can I allow users to sort a protected table using dropdowns?
I'm trying to create a table where users can't edit the content of the cells but can edit the ordering of the table- what's the best way to achieve this? Stuart, I've written this code some time back for one of my users and it works perfectly for them. Maybe you would like to adapt to suit your needs.. Sub Sort_pr_range() ActiveSheet.Unprotect 'Unprotect the sheet Range("A1:F100").Sort Key1:=Range("B1"), _ 'where "B1" will be the sort pri Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase...

looking for user in a home furnishing store
I have a prospect running a home furnishings store who would like to speak with someone using RMS in a similar type store (sofas, chairs, tables, lighting, and associated decor) My prospect is in Wellesley, MA. How can I find a local user, or at least a similar user that my prospect can talk with? -- FRB I am using rms for out door furniture tables chairs and more what are you looking for what can i do to help "FRB" wrote: > I have a prospect running a home furnishings store who would like to speak > with someone using RMS in a similar type store (sofas, chairs, tab...

Workflow -Assembly Call
How does add subtract in the Workflow>> assembly call works. I do understand that ypu can write your own custom workflows, register it and then call them from here, but I am pretty confused with the existing functionality. workflows can call other workflows workflows can also call a method in a .net assembly. ie you could write a piece of code in c# or vb.net and then have the workflow call your code. ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "SAM" <SAM@discussions.microsoft.com> wrote in message news:DE...

Exchange CPU 100% only when users access the server
I have a server that has these specs: Win 2000 SP4 (fully updated) Exchange 2000 SP3 (fully updated) 1.3 Ghz 1GB RAM that is going hay-wire. The server seems to run fine when no one is accessing the server, but when the server starts receiving requests from the users, the CPU pegs at 100%. Another funny factor that is strange is the database size. The database keeps growing... We started from a freah defragged database this morning at ~7GB and now, as I type this, the database has grown to be the size ~11.2GB. I have looked at the mailboxes and I dont see any apparent change in their ma...

Async calls to WebService in MFC
Hey guys, I use a webservice over the net which is scripted in VB.Net. This web service receives a username and a password and returns a string structure with multiples infos. If the string is empty, I had the wrong username and/or password. Pretty simple isn't? Ok now the problem is a bit more complex. I'm using an MFC client to connect to these web services, and since they're on the internet, there's a delay between sending the data and receiving an answer. I don't know really how I can tell me client to "wait for the string". The wrapper MFC created for the w...

Invalid procedure call or argument?
Hello All This is a re-post of a problem I posted a while ago, but to which I got no suggestions: hopefully I'll be more lucky this time!? I have the following code in a Private Sub: If InStr([ResponseXML], "element") > 0 Then errorfield = "Invalid " & Mid([ResponseXML], InStr([ResponseXML], "element"), InStr(InStr(InStr([ResponseXML], "element"), [ResponseXML], "'") + 1, [ResponseXML], "'") - InStr([ResponseXML], "element") + 1) Else .... Most of the time this works fine, but for some...

Exchange server not showing quarantined attachments but users are
We recently upgraded to 2003 outlook. By default our Exchange server quarantined attachments. We have corrected the problem and proper attachments are no longer being quarantined (None are currently being held). However ALL attachments for ALL users before the upgrade are still quarantined. Any Thoughts? What do you mean by quarantined? Do you mean the attachments were blocked or are you referring to an AV client. Nue "Jacob" <Jacob@discussions.microsoft.com> wrote in message news:3BBCBF22-E721-4189-A25E-9D40D33F5A7D@microsoft.com... > We recently upgraded to 2003 o...

Unable to get DxgkDdiRecommendFunctionalVidPn call in windows 7
I am trying to add an initial vidpn topology by implementing the DxgkDdiRecommendFunctionalVidPn call in the WDDM miniport driver. But starting from windows 7 i am unable to get the DxgkDdiRecommendFunctionalVidPn function call from dxgkrnl.After searching through the MSDN documentation i found out that the function is deprecated starting from windows 7. msdn link: http://msdn.microsoft.com/en-us/library/ee220395.aspx According to msdn "On a computer running Windows 7, the display mode manager (DMM) determines an appropriate VidPN topology to apply using VidPN history d...

SystemMailbox Object User Missing
Our Exchange 2003 server is running fine. However, in one information store we have both the SMTP and System Attendant mailboxes but but the SystemMailbox is missing (is that okay?). In the other three Information Stores the SystemMailbox is there, but with no associated users. Most posts refer to http://support.microsoft.com/kb/316622 However when it discusses how to recreate the associated object user it explains how to create the SystemMailbox too. I already have those. Can't we reconnect the existing SystemMailboxes to the new user we just created in Exchange System Manager?....

Trial Project Server 2007 Functionality
Hi, I want to install a trial version of Project Server 2007. I can't find anywhere saying what functionality appears with the trial version. Do I get full functionality after installation or is it limited in any way. If it is limited, I would like to know what I get in order to make a full and proper evaluation. Thanks for your help. Fahim Microsoft trial software is full-featured. In order to evaluate all of the capability of Project Server, you'll need to use companion technologies like SQL Server 2005 or 2008, Windows Server, and Project Professional -- ...

application defined or object defined error on form
I have 2 forms. My main form which has a button to open another form(search form) and be able to select the customer that is to be viewed. I actually didn't create this form, a co-worker did and they are new to it. Well they renamed the form, table etc. It looks as if everything is changed correctly but for some reason my search form is erroring out. Not sure if it's because of the name change or something else. This selection worked before until this name change. So, On my search form, you can select a customer and click on the select button, which has this logic: varID = M...

Where to call SetWindowPos
In my SDI, can I place the following in CMainFrame::OnCreate( )? Or is there a better place? // ensure window opens on top when called by other applications, esp. the installer SetWindowPos(&wndTopMost, 0, 0, 0, 0, SWP_NOMOVE | SWP_NOSIZE); Note that wndTopMost really means it is always on top and can never have another app come on top of it. So wndTopMost is often a poor choice. SetWindowPos would not be the best choice here; SetForegroundWindow would be better. joe On Sat, 11 Aug 2007 17:04:00 -0400, "SteveR" <srussell@removethisinnernet.net> wrote: >...

Call out to detect attaching files
Hi All, Is there a method to write a callout to detect file uploads. i knoe that when i file is uploading it's going to annotation entity. i have written preupdate and postupdate methods to track this. but when i add a file the event doesn't get fired and doesn't indicate the changes. later if i do a change to the note then i'm able to see the changes. can anyone tell me the reason... Thanks, Charith On Aug 7, 2:34 am, Charith <Char...@discussions.microsoft.com> wrote: > Hi All, > > Is there a method to write a callout to detect file uploads. i knoe tha...