Returns TABLE based on a condition

HI all,

In SQL Server, i have a function which will return a table. like

create function fn_test (@.t int) returns table as
return (select * from table)

now i want the function to retun the table based on some condition like below

create function fn_test(@.t int) returns table as
if @.t = 1 return (select * from table1)
else return (select * from table2)

It is not working for me. Please give me your suggesstions. It's very urgent.

Thank you in advance...[posted and mailed, please reply in news]

Sreeneet (srinit@.ctd.hcltech.com) writes:
> create function fn_test (@.t int) returns table as
> return (select * from table)
> now i want the function to retun the table based on some condition like
> below
> create function fn_test(@.t int) returns table as
> if @.t = 1 return (select * from table1)
> else return (select * from table2)
> It is not working for me. Please give me your suggesstions. It's very
> urgent.

You will have to write a multi-step function, you cannot do that an
inline function.

CREATE FUNCTION fn_test(@.t int)
RETURNS @.tbl TABLE (col1 int NOT NULL,
...) AS
BEGIN
IF @.t = 1
INSERT @.tbl (...) SELECT ... FROM table1
ELSE
INSERT @.tbl (...) SELECT ... FROM table2
RETURN @.t
END

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp