Can I use if statement in a table valued function?

hi,

I am using a function in sql server 2005 like this:

...... myfunction(... @.FlagOn int)

.......

begin

return

(

if(@.FlagOn = 1)

select * from.......

else

select * form....

)

end

But it keeps complaining there is some syntax error around if. What is it?

Thanks.

No. Table valued function won’t allow this. Because it might cause more than one schema definition for the table valued function, which is not possible in the database.

If it is a filter then you can attach the additional condition on the where clause. If the flag is used to fetch different columns or different table create new function for each flag.

If the final schema is same (same number of columns and identical datatype for both the flags), use table valued function (not inline table valued function).

Code Snippet

Create function getvalues(@.flag as int)

Returns @.result table (id int, name varchar(100))

As

Begin

If @.flag=1

Insert into @.result

Select Top 10 id, name from sysobjects

Else

Insert into @.result

Select Top 10 id,name from syscolumns

return;

End

Go

Select * from getvalues(1)

go

Select * from getvalues(0)

|||

Thanks lot, it works great.

Another thing is, can I create index on table @.result?

Coz I am thinking something like this:

select * from getvalues(1) as s where s.ID > 100

If it has index on ID, it might be faster.

|||

Maximum you can add Primary Key/Unique key on the table variable. If your values are unique then you can go for it.

@.result table (id int primary key, name varchar(100))