Hello Folks,
You would be very much aware of the View in SQL Server. But do you really know about SQL Server Inline Table Valued Functions?
Well you don’t have to worry further more on this, because I will let explain you in a brief.
With the introduction of SQL Server 2000, Microsoft has offered two types of table-valued functions, i.e., Inline and Multi-Statement Table-Valued Functions. But now I am going to deal with the Inline table-valued functions.
As you would had seen in my previous blog-post, that how a scalar function returns a single value. If you want to see it again, then here is the link;
Here are some keynotes about Inline Table-Valued Functions:
- It has no BEGIN/END body, and the SELECT statement is being used to return a virtual table.
- You can see the syntax for this:
CREATE FUNCTION [owner_name.]function_name
( [ { @parameter_name data_type
[ = default ] } [ ,…n ] ] )RETURNS TABLE
[ WITH < function_option > [ ,…n ] ] [ AS ]RETURN [ ( ] select_statement [ ) ]
Now, the concept if Inline Table-Valued Functions will become clearer if you see the following example. It is a very simple one, watch it carefully-
USE TEST; CREATE FUNCTION ftStudList() RETURNS Table AS RETURN( SELECT dbo.Students1.[SID] AS Stud_Id1,dbo.Students1.Name AS Stud_Name1,dbo.Students1.City AS Stud_City1, dbo.Students2.[SID] AS Stud_Id2,dbo.Students2.Name Stud_Name2,dbo.Students2.City AS Stud_City2 FROM dbo.Students1 INNER JOIN dbo.Students2 ON dbo.Students1.[SID]=dbo.Students2.[SID]);
Now, Calling an Inline Table-Valued Function:
- One thing I discovered from the above example is that, while you are creating a function you are not at all being allowed to show the table in which two columns have the same name, i.e., every column name should be unique.
Now, the question arises are Inline Table-Valued Functions are really better than a View…If Yes, then Why?
Let say, if you want a subset of rows form a view, then you need to apply a search criteria in a WHERE clause. But an Inline Table-Valued Functions can accept search criteria as function parameters within the pre-compiled SELECT statement, which gives more ease as well as flexibility to the users and developers of SQL.
It will become clearer to you, if you see an example below:
CREATE FUNCTION ftStudListbyId(@Id INT = NULL) RETURNS Table AS RETURN( SELECT dbo.Students1.[SID] AS Stud_Id1,dbo.Students1.Name AS Stud_Name1,dbo.Students1.City AS Stud_City1, dbo.Students2.[SID] AS Stud_Id2,dbo.Students2.Name Stud_Name2,dbo.Students2.City AS Stud_City2 FROM dbo.Students1 INNER JOIN dbo.Students2 ON dbo.Students1.[SID]=dbo.Students2.[SID] WHERE dbo.Students1.[SID] = @Id OR @Id IS NULL);
Now, if you want to see the Student whose Id is 1, than you just have to write the query statement like:
If you want to display all the Students irrespective of their Id’s, then write a query like:
Well, this was a bit about Inline Table-Valued Functions.
In the next post I would like to deal with Multi-Statement Inline Table Valued Functions.
So be tuned and also comments on this!!
Regards
Piyush Bajaj
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
good one Piyush !
Thank you, it’s very helpful.
Excellent…good to know. Very useful. Thanks…keep it up.
Good one!!