Thursday, April 21, 2011

Can I use if statement in a table valued function?

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



            If @flag=1

                        Insert into @result

                        Select Top 10 id, name from sysobjects


                        Insert into @result

                        Select Top 10 id,name from syscolumns







Select * from getvalues(1)




Select * from getvalues(0)

No comments:

Post a Comment