[dba-SQLServer] Calling function from a query

You'd still need this function to return something so your where clause
would have to be
WHERE Account_IsCancelled(a) = 1


Your function would probably read more like this:
Function fn Account_IsCancelled(a as Account) RETURNS INT AS
BEGIN
IF EXISTS( SELECT * FROM Accounts a Where (a.AccountStatusCode <> 'M' and
a.DateCancelled is not null))
BEGIN
RETURN 1
END
ELSE
BEGIN
RETURN 0
END
END
On 10/23/05, Darren DICK <darrend at crhorizons.com.au> wrote:
>
> 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
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...