Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to execute Table valued function

I have following function which returns Table .

create Function FN(@Str varchar(30))   returns   @Names table(name varchar(25))   as    begin         while (charindex(',', @str) > 0)       begin       insert into @Names values(substring(@str, 1, charindex(',', @str) - 1))      set  @str = substring(@str, charindex(',', @str) + 1, 100)         end       insert into @Names values(@str)          return   end 

Could any one please explain me how to run this function.

like image 905
Shine Avatar asked Aug 05 '11 08:08

Shine


People also ask

How do you run a table valued function object?

You can execute it just as you select a table using SELECT clause. In addition you can provide parameters within parentheses.

How do you call a table valued function in SQL query?

A table-valued function returns a single rowset (unlike stored procedures, which can return multiple result shapes). Because the return type of a table-valued function is Table , you can use a table-valued function anywhere in SQL that you can use a table.

What is table valued function?

A table function, also called a table-valued function (TVF), is a user-defined function that returns a table. You can use a table function anywhere that you can use a table. Table functions behave similarly to views, but a table function can take parameters.

How do you execute a scalar valued function in SQL?

Scalar-valued functions can be executed by using the EXECUTE statement. If you EXECUTE a function rather than use it in a SELECT statement or constraint, you can leave out the schema name in the function name, and it will look in the dbo schema followed by the users default schema.


2 Answers

A TVF (table-valued function) is supposed to be SELECTed FROM. Try this:

select * from FN('myFunc') 
like image 157
Paul Creasey Avatar answered Sep 21 '22 07:09

Paul Creasey


You can execute it just as you select a table using SELECT clause. In addition you can provide parameters within parentheses.

Try with below syntax:

SELECT * FROM yourFunctionName(parameter1, parameter2) 
like image 31
Shiham Avatar answered Sep 22 '22 07:09

Shiham