SQL Server 2000: UDF calls

If function returns a table, use:

  • SELECT * FROM dbo.udf()


If returns a single value (Scalar) use:

  • SELECT dbo.udf()

Don't forget the brackets () even if no paramters or SQL Server 2000 will throw up unknow function


Return a table with dynamic columns

create function [xxxx].[isAccessionValid](@accessionNumber varchar(40))

returns table as

return (

    select accessionnumber, transferaccepteddate, ad.number as documentationnumber, ad.title  as documentationtitle,

    recordsdescription, deaccessionstate, deaccessioneddate, accessionincomplete as incomplete, holdingslocation

    from gladis.accession t

    join ACCESSIONDOCUMENTATION ad on t.oid = ad.entityoid

    where accessionnumber = @accessionNumber and versionnumber = (select max(versionnumber) from gladis.accession

    where accessionnumber = t.accessionnumber) and ad.flag=1