Home » Interview Q&A » User defined functions in sql

User defined functions in sql

Kill Processes in sql server 2008
Is Application running already in WPF

Hey folks till now we have discussed Kill Processes in sql server 2008, View in sql default constraint in sql , Remove Cross duplicate rows in SQLRecursive SQL Query , Recursive SQL Query-2STUFF and CONCAT in SQLRANK in SQL server , Difference between temporary table and table variable in sql serverUNIQUEIDENTIFIER in SQL ServerRAW Mode with FOR XML , AUTO Mode with FOR XMLEXPLICIT Mode with FOR XML , PATH Mode with FOR XMLOUTPUT Clause in SQL ServerDifference between delete and truncate in sql server etc.

Today we will discuss regarding User defined functions in sql.

There are three types of user defined functions in sql as below.

  1. Scalar Function
  2. Inline Table-Valued Function
  3. Multi-Statement Table-Valued Function

Before we proceed to each of this function in detail lets first have a look at the pros and cons of user defined function.

  1. We can not use INSERT, UPDATE or DELETE or any action that change the database state in UDFs.
  2. We can not use OUTPUT INTO clause that has a table as its target.
  3. UDFs can not return multiple result sets.
  4. We can not use error handling in UDFs. It does not support TRY…CATCH, @ERROR or RAISERROR.
  5. UDFs can not call a store procedure but a store procedure can call UDF.
  6. Dynamic SQL or temp tables are not allowed in UDFs while table variables are allowed.
  7. The FOR XML clause is not allowed.
  8. UDFs must always return a value either a scalar value or a table.
  9. UDFs doesn’t allow Transaction Management.

1. Scalar user defined function

A scalar function is a user defined function which will return single value. Following function will accept department id as a integer and returns department name as a output.

The following command will return the department ” based on the supplied input parameter.

SELECT DBO.[ufnGetDepartmentName](1) AS DepartmentName

Output : Research and Development

2. Inline Table-Valued user defined Function

An inline table valued function will return the table which is derived from single select statement. There is no need to specify table variable name or column names as it is directly derived from select statement.  Duplicate column are not allowed. It can be a best alternative to the view in sql.

3. Multi-statement table valued user defined function

Multi statement table valued function will return a table which can be a out come of one or more sql statements. Unlike inline table valued function here we must have to define the table structure that is being returned.

Execute the above function as per below select statement.

SELECT * FROM FNGETEMPLOYEEDETAILS(1)

About

Hello friends, My self Dhruv Sheth, I have more than 6 years of Industrial experience as a Software Developer. In my career I got chance to work with asp.net, c#,Web API, JavaScript, JQuery, WPF, WCF , Windows Application, PostScript, vb.net, GhostScript etc. So I have decided to share my knowledge with everyone and finally I have come up with TechnoThirsty.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Time limit is exhausted. Please reload CAPTCHA.




DISCLAIMER:
The content is copyrighted to technothirsty.com and may not be reproduced on other websites without permission from the owner.You may contact us using the information below.