In a simple case you can create an Inline Table-valued Function,
SELECT TOP 1 * FROM (
select id from customers where cli=@cli
UNION ALL
select 999 as id) dt1 ORDER BY id
Otherwise you can create a Multi-statement Table-valued Function. There's an example of such function in BOL under CREATE FUNCTION.
>Can anyone tell me how to create a UDF in SQL Server 2000 that returns a TABLE where the definition contains an IF statement?
>
>I wanted to do something like:
>
>create function fn_foo(@cli int) returns table as
>
>return
>
>if exists(select id from customers where cli=@cli)
>select id from customers where cli=@cli
>
>else
>select 999 as id
>
>
>
>or something a bit more complex.
>
>If this doesn't work, is it possible to put a CASE construct in a WHERE clause in a SQL SELECT statement?
>
>TIA,
>Simon
--sb--