Prev: any word order match
Next: Fastest way to move lots of data between databases on same ser
From: Mij on 11 Jun 2010 09:47
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 ***
From: Mij on 11 Jun 2010 11:57
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 ***
From: Erland Sommarskog on 11 Jun 2010 18:20
Mij (mdsj(a)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(a)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

 | 
Pages: 1
Prev: any word order match
Next: Fastest way to move lots of data between databases on same ser