Table-Valued User-Defined Functions in SQL Server

In previous blog Scalar User Defined Function in Sql Server discuss about scalar UDF .Now i am explaining Table Valued UDF.

Table Valued UDF is like views but slightly better from views. It return type is table means it can return more than one rows. The table returned by Table-Valued UDF can be used in FROM clause of a T-SQL Statements.

Illustrate with an Example:

First of all create a table tbl_login in sql server 2005 database. It has following field:
 

Column Name

Data Type

Description

user_id

Int

Identity(1,1) Primary key

Username

varchar(50)

Login user name

Password

varchar(50)

Login user password

(A)Create Table-Valued UDF:

Here we create Table Valued UDF [dbo].[LoginInfo] for generate a unique username according his/her email address and password and check these user name that is already exist or not. Here Table-Valued UDF  [dbo].[LoginInfo] has one parameter that is varchar(30) type.

Create function [dbo].[LoginInfo](@email varchar(30)) /*function Name*/
returns @tbl_user table(user_name varchar(20),passowrd varchar(20)) /* table that will be return*/
as
begin
declare @index int
declare @user varchar(20)
declare @pass varchar(20)
declare @count int
select @index= charindex('@',@email) /* get index value where '@' char exist in @email variable */
select @user=substring(@email,0,@index) /* Get Substring from @email variable of dynmic length */
select @pass= substring(@email,1,3)+ cast((select (count(*)+100) from tbl_login)  as varchar(50))
 select @count=count(*) from tbl_login where username=@user /* check existing user in table*/
if(@count=0)
insert into @tbl_user(user_name,passowrd) values(@user,@pass)
return
end

(B)Implementation of  Table-Valued UDF:

Here Two satement one for call function dbo.LoginInfo and another for use to insert value intable.It mostly use in T-SQL statement means we can use it in  store procedure.Here we pass parameter in function an email address.

declare @username varchar(50)
declare @password varchar(50)
select @username=user_name,@password=passowrd from dbo.LoginInfo('sandeep.shekhawat88@gmail.com')/* function call*/
if(@username!=' ')/*check for NULL*/
insert into tbl_login(username,password) values(@username,@password)/*insert values in table*/

(C)Output of Table-Valued UDF:

select * from tbl_login

user_id

username

Password

1

sandeep.shekhawat88

san100