user defined function in sql

Disclaimer: This presentation is prepared by trainees of baabtra as a part of mentoring program. This is not official document of baabtra –Mentoring PartnerBaabtra-Mentoring Partner is the mentoring division of baabte System Technologies Pvt . Ltd

Week Target Achieved

1 ------- 15wpm

2

3

Typing Speed

Jobs Applied# Company Designation Applied Date Current Status

1

2

3

User Defined Functions In SQL

Sandeep.vvsandeep.ard@gmail.comwww.facebook.com/

sandeep.narayan.73twitter.com/usernamein.linkedin.com/in/sandeep

v9447532678

What is UDF?

• User defined functions are similar to ordinary functions found in programming languages.

• The main purpose of UDF is code reusability.

Benefits of using UDF

• Modular programming.• Faster execution.• Code reusability.

• User defined functions can be passed input parameters

• User defined functions are compiled and executed at runtime so pretty slower than stored procedures

• UDF can’t perform DML (data manipulation language) operations like Insertion, Update and Deletion on the base table

• UDF can’t return non deterministic values like GETDATE () etc

• Stored procedure can’t be called from inside a UDF whereas a stored procedure can call a user defined function or another stored procedure inside it

Types of UDF…

•Scalar functions

•Returns a single value

•Inline functions

•Returns a table

•Table values functions

• Multiple operations, complex logic just like Stored procedures

FORMAT OF A UDF

CREATE FUNCTION dbo.function_name ( @parameter1 datatype = default value, @parameter2 datatype ) RETURNS datatype AS BEGIN

sql statements foms function body ... RETURN value END

SCALAR FUNCTION

CREATE FUNCTION dbo.teach_name(@teachr_id int)RETURNS varchar(20)ASBEGIN Declare @returnvalue varchar(20)Select @returnvalue=teacher_name from tbl_teachers where teacher_id=@teachr_id RETURN @returnvalueEND

Execution: select dbo.teach_name(1002)as teacher_name

INLINE FUNCTION

CREATE FUNCTION dbo.list_teach(@name varchar(10))RETURNS TABLEASRETURN SELECT *FROM tbl_teachers where teacher_name like '%'+@name+'%'

Execution: select * from dbo.list_teach('i')

Table values functions

CREATE FUNCTION udf_teach_day()RETURNS @Result TABLE ( vchr_teacher_name varchar(20) ,vchr_day varchar(10) )ASBEGIN INSERT INTO @Result (vchr_teacher_name,vchr_day) select teacher_name,datepart(dw,teacher_doj)as vchr_day from tbl_teachers UPDATE @Result SET vchr_day = 'weekday' where vchr_day<>cast(2 as varchar(20)) UPDATE @Result SET vchr_day = 'monday' where vchr_day=cast(2 as varchar(20)) RETURNEND

Execution:Select * from udf_teach_day()

ConclusionUDF are similar to functions in the programming languages, they provide a mechanism for extending the functionality of the database server byadding a function that can be evaluated in SQL statements.

If this presentation helped you, please visit our page facebook.com/baabtra and like it.

Thanks in advance.

www.baabtra.com | www.massbaab.com |www.baabte.com

Contact Us

Emarald Mall (Big Bazar Building)Mavoor Road, Kozhikode,Kerala, India.Ph: + 91 – 495 40 25 550

NC Complex, Near Bus StandMukkam, Kozhikode,Kerala, India.Ph: + 91 – 495 40 25 550

Start up VillageEranakulam,Kerala, India.

Email: info@baabtra.com