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.
A TVF (table-valued function) is supposed to be SELECTed FROM. Try this:
select * from FN('myFunc')
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)
If you want to use a field from the returned table in a local variable you can use the following:
SELECT Top 1 @Name=[Name] from [dbo].FN('Some text')
I have a similar problem in that I have a TVF that takes a latitude and longitude and returns a location as an Easting/Northing pair and I need to extract each value in turn. This is how I solved accessing the individual fields.
