In this post I will write some multi statement table valued function examples. This type functions creates a new table inside and returns its values back.
Example -1 Sql Function For Fibonacci Series
The Fibonacci Sequence is the series of numbers:0, 1, 1, 2, 3, 5, 8, 13, 21, 34,
The next number is found by adding up the two numbers before it.
First, we must create the function, the function’s code is below
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE FUNCTION fn_Fibonacci(@max int) RETURNS @numbers TABLE(number int) AS BEGIN Declare @n1 int = 1,@n2 int =1,@n3 int=0,@temp int Insert Into @numbers Values(@n1),(@n2) WHILE (@n3<=@max) BEGIN set @n3 = @n2 + @n1 set @temp = @n2 set @n2 = @n3 set @n1 = @temp Insert Into @numbers Values(@n3) END RETURN END |
If you get succesful message then you can execute the code below
Example-1
|
1 |
Select * from dbo.fn_Fibonacci(40) |
The Result Of Code
Example-2
|
1 |
Select * from dbo.fn_Fibonacci(200) |
The Result Of Code
Example -2 Sql Split Function
The function splits a string by a delemiter to a table. It gets two parameter;the first parameter is string value and the second one is the delemeter character. The function’s code is below first run it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE FUNCTION dbo.fnSqlSplit( @stringValue VARCHAR(8000), -- List of delimited items , @delemiter VARCHAR(1) = ',' -- delimiter that separates items the default value is comma ) RETURNS @List TABLE (item VARCHAR(8000)) BEGIN DECLARE @sItem VARCHAR(8000) WHILE CHARINDEX(@delemiter,@stringValue,0) <> 0 BEGIN SELECT @sItem=RTRIM(LTRIM(SUBSTRING(@stringValue,1,CHARINDEX(@delemiter,@stringValue,0)-1))), @stringValue=RTRIM(LTRIM(SUBSTRING(@stringValue,CHARINDEX(@delemiter,@stringValue,0)+LEN(@delemiter),LEN(@stringValue)))) IF LEN(@sItem) > 0 INSERT INTO @List SELECT @sItem END IF LEN(@stringValue) > 0 INSERT INTO @List SELECT @stringValue -- Put the last item in RETURN END |
When you execute the function you will get succesful message then you can use the code below.
Example1
|
1 |
Select * from dbo.fnSqlSplit('12,23,34,45',',') |
The Result of Query
Example2
|
1 |
Select * from dbo.fnSqlSplit('Amelia-Olivia-Isla-Sophia-Isabella','-') |
The Result of Query
Example3
|
1 |
Select * from dbo.fnSqlSplit('12,5;2,6;45,9;40,5;14,5',';') |
The Result of Query
Example -3 Sql Function For Fibonacci Sequence / Solution 2
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Create FUNCTION fn_Fibonacci2(@count int) RETURNS @numbers TABLE(number int) AS BEGIN Declare @n int,@i int=0 Insert Into @numbers Values(0),(1) WHILE (@i<@count) BEGIN Select @n = sum(number) from (Select top 2 number from @numbers order by number desc) as T Insert Into @numbers Values(@n) Select @i = count(*) from @numbers END RETURN END |
Example-1
|
1 |
Select * from dbo.fn_Fibonacci2(5) |
The Result Of Code
Example-2
|
1 |
Select * from dbo.fn_Fibonacci2(15) |
The Result Of Code
