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('[email protected]')/*
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 |