>Hi
>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

Simon,

In case you come across the need to write a table-valued function that cannot be optimized into an inline table, here is what a multi-statement table valued function looks like:

CREATE FUNCTION udf_myfunc (@cli int) RETURNS @Result TABLE (
[id] int NOT NULL) AS
BEGIN
IF EXISTS (SELECT [id] FROM customers WHERE [cli] = @cli)
INSERT INTO @Result SELECT [id] FROM customers WHERE [cli] = @cli
ELSE
INSERT INTO @Result SELECT 999 as [id]

RETURN @Result
END