User defined Function in SQL

User defined Function in SQL

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

2. Week Target Achieved 1 ------- 15wpm 2 3 Typing Speed

3. Jobs Applied # Company Designation Applied Date Current Status 1 2 3

4. User Defined Functions In SQL Sandeep.v [email protected] www.facebook.com/sandeep.narayan.73 twitter.com/username in.linkedin.com/in/ sandeep v 9447532678

5. What is UDF? User defined functions are similar to ordinary functions found in programming languages. The main purpose of UDF is code reusability.

6. Benefits of using UDF Modular programming. Faster execution. Code reusability.

7. 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

8. 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

9. 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

10. SCALAR FUNCTION CREATE FUNCTION dbo.teach_name (@ teachr_id int ) RETURNS varchar (20) AS BEGIN Declare @ returnvalue varchar (20) Select @ returnvalue = teacher_name from tbl_teachers where teacher_id =@ teachr_id RETURN @ returnvalue END Execution: select dbo.teach_name (1002)as teacher_name

11. INLINE FUNCTION CREATE FUNCTION dbo.list_teach (@name varchar (10)) RETURNS TABLE AS RETURN SELECT * FROM tbl_teachers where teacher_name like '%'+@name+'%' Execution: select * from dbo.list_teach (' i ')

12. Table values functions CREATE FUNCTION udf_teach_day () RETURNS @Result TABLE ( vchr_teacher_name varchar (20) , vchr_day varchar (10) ) AS BEGIN 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)) RETURN END

13. Execution:Select * from udf_teach_day ()

14. Conclusion UDF are similar to functions in the programming languages, they provide a mechanism for extending the functionality of the database server  by adding a function that can be evaluated in SQL statements.

15. 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

16. Contact Us Emarald Mall (Big Bazar Building) Mavoor Road, Kozhikode, Kerala, India. Ph: + 91 – 495 40 25 550 NC Complex, Near Bus Stand Mukkam , Kozhikode, Kerala, India. Ph: + 91 – 495 40 25 550 Start up Village Eranakulam , Kerala, India. Email: [email protected]