Returns TABLE based on a condition

[Sreeneet]

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

[Erland Sommarskog]

[posted and mailed, please reply in news]

Sreeneet (sr****@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, es****@sommarskog.se

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