User Defined Function - Condition based tabel return ???

[Paw Jershauge]

I have a colleague, that has a bit of trouble creating a UDF heres there code but it fails:

CREATE FUNCTION GetData(@Param int)
RETURNS TABLE
AS
RETURN
IF @Param = 1
SELECT * FROM Emp
ELSE
SELECT * FROM Offices
END

The function shall return different table based oppon the condition of the @Param value. Can someone help ???

With great code, comes great complexity, so keep it simple stupid...:-\ :-\

[PIEBALDconsult]

I haven't written a UDF or a while, and I don't like that design, but can you use a UNION?

SELECT * FROM Emp WHERE @Param=1
UNION ALL
SELECT * FROM Pffices WHERE @Param=0

[Paw Jershauge]

Thanks I will let him know this, lets see if it works ;) Thanks for the reply

With great code, comes great complexity, so keep it simple stupid...:-\ :-\

[Ravi Sant]

No this isn't possible with either an inline or multi statement TVF (unless the 2 tables are union compatible?) so the above reply is only workable solution, if tables are union compatible.

[Mycroft Holmes]

This is not a valid use for a function, a stored proc maybe but not a function. There is no way that emp and offices are going to be union compatible, if they are then why are they in different tables. You need to rethink your design.

Select * from anytable

This is frowned upon, explicitly declare the fields.

Never underestimate the power of human stupidity RAH