[dba-SQLServer] dba-SQLServer Digest, Vol 32, Issue 13

Darren:


Hmmmm...  I'm not sure I follow what you want, but here are a few
thoughts:  

1) functions are evaluated for each row in the query.

2)  The methods for calling a function are covered in BOL

3)  Would the following be an acceptable way of achieving your goal:

SELECT a.* from Account a
WHERE a.AccountStatusCode<>'M' 
AND a.Cancelled IS NOT NULL

?  Not sure why using a function is preferable to this very basic
approach?


Or, to put it in a subquery, perhaps something like

SELECT a.* from Account a
WHERE a.[Primary Key] NOT IN
	(SELECT b.[Primary Key] FROM Account b
	WHERE b.AccountStatusCode<>'M' 
	AND b.Cancelled IS NOT NULL)

Or, to use a view...

CREATE VIEW vw_CancelledAccounts
AS
SELECT a.[Primary Key] from Account a
WHERE a.AccountStatusCode<>'M' 
AND a.Cancelled IS NOT NULL

Then use it in the query as:

SELECT a.* from Account a
INNER JOIN vw_CancelledAccounts b
ON a.[Primary Key]=b.[Primary Key]


There are many other ways to get there, too.  I don't see how using a
function is the best choice... 

HTH.  D. Lewis



Message: 1
Date: Mon, 24 Oct 2005 16:03:48 +1000
From: "Darren DICK" <darrend at crhorizons.com.au>
Subject: [dba-SQLServer] Calling function from a query
To: <dba-sqlserver at databaseadvisors.com>
Message-ID: <200510240600.j9O60JJ28520 at databaseadvisors.com>
Content-Type: text/plain;	charset="us-ascii"

Hello all

What I have is a function that will (in this case) determine those
records from the Account table that are now 'cancelled'

In this example the cancelled status is true if there is a cancelled
date in the Account Table  and the Account status code is M Those 2
things combined need to occur to determine a cancelled record

So rather than copy and paste the contents of the function below into
each and every occurrence of testing for 'cancelled'
I want a function to determine those 2 criteria then select all records
from the Account table that meet the criteria as determined in the
function

IE how do I call the function from the query and do it 'on the fly'

Make sense?

Lemme know

Many thanks in advance

Darren

___________________________________________________
SQL = select * from Account  where  Account_IsCancelled(a)
    
FUNCTION = fn Account_IsCancelled(a as Account) begin
    return (a.AccountStatusCode <> 'M' and
            a.DateCancelled is not null) end

Darren