User Defined Functions in SQL Server

In this article, I am giving a quick overview about user defined functions in SQL Server. After completing this article you will understand:

What is User Defined Functions (UDF) in SQL Server?

What are the types of User Defined Function in SQL Server?

How to create User Defined Function in SQL Server?

How to ALTER and DROP user defined functions in SQL Server?

What are the advantages of User Defined Functions in SQL Server?

Please give your valuable suggestions and feedback to improve this article.

I have already discussed about TSQL programming basics, I will recommend you to visit the article if you are not aware of TSQL.

I hope you have gone through the basics of TSL Programming basics, now we can start.

As you know we have two types of blocks in TSQL programming, Anonymous Blocks and Sub-Program Blocks.

Sub Program blocks are of two types in SQL Server.

1. Procedures

2. Functions.

In this article I will be focusing on Functions, I have already discussed about Stored Procedure in my previous article.

There are two types of Functions in SQL Server

1. Built In Function: These are the inbuilt functions present in SQL Server. User can’t make any change in these functions i.e Min(), Max(), UPPER()
2. User Defined Functions: SQL Server allows user to create their own functions. These functions are known as User Defined Functions.

What is User Defined Functions (UDF) in SQL Server?

A user defined functions are also a Stored Block of code similar to Stored Procedure. It always returns at least a single value or a table. Function doesn't support output parameters but it can have maximum 1024 input parameters. We can’t perform DDL, DML operations in functions. There are many limitations in functions i.e we can’t use Try-catch blocks in functions. For more about stored procedure and function refer the article Difference between Stored Procedure and Function

What are the types of User Defined Function in SQL Server?

Functions are of 3 types in SQL Server.

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

How to create User Defined Function in SQL Server?

Let’s understand each type of User Defined Functions with one simple example.

Scalar Functions

A Scalar user-defined function returns one of the scalar data types referenced in the RETURNS clause in CREATE FUNCTION statement. Text, ntext, image, Cursor and timestamp data types are not supported in Scalar User Defined. Scalar Function can have 0 to 1024 input parameters.

Below is the syntax to create Scalar Type User Defined Functions

Syntax

CREATE FUNCTION Function-Name

(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)

RETURNS Data-Type

WITH Function-Options

AS

BEGIN

Function-Body

RETURN Scalar-Expression

END

Function-Name– This is the function name, you have to remember this name.

@Paramter-Name Data-Type = Default – This is the input parameter name with its data type for the function.

Function-Options – Functions options can be any of these two

1. Encryption – Indicates that the Database Engine encrypts the catalog view columns that contains the text of the create function statement.
2. Schemabinding – Indicates that Functions is bound to the database object that it references. Object can’t be dropped until you drop the function or alter the Function without Schemabinding option.

Function-Body – This is the place where we write our logic.

Example

I am creating one EMP table and populating some data in this table. We will be using this table to understand Scalar type user defined functions.

You can use below query to populate dummy data.

USE TEACHMESQLSERVER

GO

CREATE TABLE EMP (EMPID INT, EMPNAME VARCHAR(255), DEPNAME VARCHAR(255), SALARY MONEY, BONUS MONEY)

INSERT INTO EMP VALUES(1,'GHANESH','IT',2000,100)

INSERT INTO EMP VALUES(2,'PRASAD','HR',3000,1100)

INSERT INTO EMP VALUES(3,'GAUTAM','SALES',2500,400)

INSERT INTO EMP VALUES(4,'ANVIE','MARKETING',20000,NULL)

GO

SELECT * FROM EMP

Problem – Create a function which returns Employee’s salary + Bonus from EMP table based on EMPID.

SQL Code –

CREATE FUNCTION MYSCALARFUNCTION (@EMPID INT)

RETURNS MONEY

AS

BEGIN

DECLARE @TOTALSALARY MONEY

SELECT @TOTALSALARY= SALARY+ ISNULL(BONUS,0) FROM EMP WHERE EMPID=@EMPID

RETURN @TOTALSALARY

END

Congratulations you have successfully created your first user defined function which will return (SALARY + BONUS) from EMP Table based on their EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Scalar-Valued Functions. Below Images shows the path. 

Now our task is to call recently created a scalar function.

Syntax for calling a Scalar Function:

SELECT <owner>.<function-name> (Parameters values)

Calling the MYSCALARFUNCTION function:

SELECT DBO.MYSCALARFUNCTION(2) as TOTALSALARY

 As you can see from above result our MYSCALARFUNCTION function is returning only one scalar value which as referenced in the CREATE Function command.

Inline Table Valued Functions

Inline Table Valued Functions return a Table variable as an output. In Inline table valued functions, the Table returns value is defined through a single select statement so there is no need of BEGIN/END blocks in the CREATE FUNCTION statement. There is also no need to specify the table variable name or column definitions for the table variable because the structure of the table variable will be generated from the columns that compose the SELECT statement. In Inline Table Valued Functions there should be no duplicate columns referenced in the SELECT statement, all derived columns must have an associated alias.

Syntax

CREATE FUNCTION Function-Name

(@Parameter-Name Data-Type = Default, @Parameter-Name Data-Type = Default …. n)

RETURNS TABLE

WITH Function-Options

AS

RETURN (SELECT STATEMENT)

Problem – Create a function which returns EMPNAME, DEPNAME and SALARY from EMP table based on SALARY range.

SQL Code –

CREATE FUNCTION MYINLINETABLEVALUEDFUNCTION (@SALARY INT)

RETURNS TABLE

AS

RETURN (SELECT EMPNAME, DEPNAME, SALARY FROM EMP WHERE SALARY>@SALARY)

Congratulations you have successfully created your first Inline Table Valued user defined function which will return EMPNAME, DEPNAME and SALARY from EMP Table based on SALARY range. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path. 

Now our task is to call recently created a scalar function.

Syntax for calling a Table Valued Function:

SELECT *|<Column List> from <function-name> (Parameters values)

Calling the MYINLINETABLEVALUEDFUNCTION function:

SELECT * from MYINLINETABLEVALUEDFUNCTION (2000)

As you can see from the above result set, our table valued function is returning a table which has three columns. Structure of the table variable was generated by the select statement.

Multi-Statement Table Valued Functions

Multistatement Table Valued functions are similar to the Inline Table Valued Function but the body of the body of this function can contain multiple statements and the structure of the table can be defined by the user.

Below is the syntax to create Multistatement Table Valued Type User Defined Functions

Syntax

CREATE FUNCTION Function-Name

(@Paramter-Name Data-Type = Default, @Paramter-Name Data-Type = Default …. n)

RETURNS @Return_Variable TABLE <Table-Type-Definition>

WITH Function-Options

AS

BEGIN

Function-Body

RETURN 

END

Problem – Create a function which returns SALARY AND ANNUAL SALARY from EMP table for EMPID.

SQL Code –

CREATE FUNCTION MYMSTABLEVALUEDFUNCTION (@EMPID INT )

RETURNS @MYTABLE TABLE(SALARY MONEY, ANNUALSALARY MONEY)

AS

BEGIN

DECLARE @SALARY MONEY,@ANNUALSALARY MONEY

SET @SALARY= (SELECT SALARY FROM EMP WHERE EMPID=@EMPID)

SET @ANNUALSALARY= @SALARY*12

INSERT INTO @MYTABLE VALUES(@SALARY,@ANNUALSALARY)

RETURN

END

Congratulations you have successfully created your first Multistatement Table Valued user defined function which will return SALARY and ANNUAL SALARY from EMP Table for EMPID. You can find your recently created functions under Programmability Folder à Functions Folder à Table-Valued Functions. Below Images shows the path.

Now our task is to call recently created a scalar function.

Syntax for calling a Table Valued Function:

SELECT *|<Column List> from <function-name> (Parameters values)

Calling the MYMSTABLEVALUEDFUNCTION function:

SELECT * FROM MYMSTABLEVALUEDFUNCTION(1)

As you can see from the above result set our recently created function is returning a table variable with two columns.

How to ALTER and DROP user defined functions in SQL Server?

ALTER Function

Once you have created your functions in SQL Server, you might want to make some changes into it.

You can ALTER User Defined Functions using ALTER Statement.  At place of CREATE FUNCTION you have to use ALTER FUNCTION rest everything will remain similar to CREATE FUNCTION syntax.

Drop Function

Once you have created your functions in SQL Server, you might want to remove it from the database. You can drop User Defined Functions using Drop Statement.

Syntax

DROP FUNCTION FUNCTION-NAME

Example

DROP FUNCTION MYSCALARFUNCTION

What are the benefits of User-Defined Functions?

The advantages to SQL Server User-Defined functions are many. First, we can use these functions in so many different places when compared to stored procedure. The ability for a function to act like a table (for Inline table and Multi-statement table functions) gives developers the ability to break out complex logic into shorter and shorter code blocks. This will generally give the additional benefit of making the code less complex and easier to write and maintain. In the case of a Scalar UDF, the ability to use this function anywhere you can use a scalar of the same data type is also a very powerful thing. Combining these advantages with the ability to pass parameters into these database objects makes the SQL Server User Defined Function a very powerful tool.

What is difference between Function and Stored Procedure?

I have created a separate article for this topic.  Please click here to understand what are the differences between Stored Procedure and User Defined Functions in SQL Server?

I hope you enjoyed the article don't forget to share on Facebook.

Keep Learning!

Error Handling in SQL Server

In this article, I am giving a quick overview about Error Handling in SQL Server. After completing this article you will understand:

How to do Error Handling in SQL Server?

How to Raise Error Manually in SQL Server?

How to ADD USER Defined Error Messages in sys.messages catalog view?

Please give your valuable suggestions and feedback to improve this article.

I have already discussed about TSQL programming basics, I will recommend you to visit the article if you are not aware of TSQL.

How to do Error Handling in SQL Server?

When you have a batch of SQL statements and in that batch of SQL Statements if any SQL statement causes error then it throws the error message but it never terminates the execution of program it continues the execution of SQL statements which comes after that SQL Statement which raised error.

Let’s understand the above problem with an example

I am creating a store procedure which divides two numbers and print the result. As you can see from the below query, I am passing two number in the procedure. I am dividing them and storing the result in variable named as @RESULT. Initially I am assigning 0 values for the @RESULT variable and later updating the variable value with the result.

CREATE DATABASE TEACHMESQLSERVER

USE TEACHMESQLSERVER

GO

CREATE PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECOND INT)

AS

BEGIN

DECLARE @RESULT INT

SET @RESULT=0

SET @RESULT=@FIRST/@SECOND

PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)

END

Let’s execute the above procedure

EXEC DIVISION_BY_ZERO 20,10

 

As you can see from the result below; when we divide 20 by 10 we get 2 and our procedure prints the result as below.

Now let’s try to divide 20 by 0; its universal truth that we can’t divide any number by 0 and we will get error.

EXEC DIVISION_BY_ZERO 20,0

Here you go; we got below error message after executing the above query.

Error message clearly says that divided by zero error encountered. If you have noticed after the error message that Print Statement executed and it printed THE OUTPUT IS: 0. this is wrong right?

It should not have printed this.

To overcome this problem SQL server provides Error handling, when we do error handling the SQL statement which cause the error will be terminated and the execution of stored procedure or batch will also be stopped.

In T-SQL programming error handling is done by using TRY and CATCH construct. Errors in T-SQL code can be processed using a TRY-CATCH construct.  A TRY -CATCH construct consists of two parts TRY block and CATCH block. TRY Block contains the SQL statement which may cause error, when an error situation is detected in TYRY block, control is passed to a CATCH block where it can be processed or handled. After the CATCH block handles the exception (Error), control is then transferred to the first T-SQL statement that follows the END CATCH statement. A TRY block always starts with the BEGIN TRY statement and ends with END TRY statement. A CATCH block always starts with a BEGIN CATCH statement and ends with END CATCH statement. A CATCH block must follow a TRY block. In T-SQL each TRY block is associated with only one CATCH block.

Let’s do error handling in above procedure.

ALTER PROCEDURE DIVISION_BY_ZERO(@FIRST INT, @SECCOND INT)

AS

BEGIN

DECLARE @RESULT INT

SET @RESULT=0

BEGIN TRY

SET @RESULT=@FIRST/@SECCOND

END TRY

BEGIN CATCH

SELECT   ERROR_NUMBER() AS ErrorNumber

        ,ERROR_SEVERITY() AS ErrorSeverity

        ,ERROR_STATE() AS ErrorState

        ,ERROR_PROCEDURE() AS ErrorProcedure

        ,ERROR_LINE() AS ErrorLine

        ,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

PRINT 'THE OUTPUT IS:'+CAST(@RESULT AS VARCHAR)

END

As you can see from the above code, we have kept SET @RESULT=@FIRST/@SECOND in TRY Block. It is because this line of SQL statement is creating dividing by zero error. We have CATCH block next to this TRY block. CATCH block is handling the error raised by its TRY block.

Let’s try to divide 20 by 0.

GO

EXEC DIVISION_BY_ZERO 20,0

As you can from the above output, this time we didn't get any error message but we get a message saying Divide by Zero encountered and it also terminates the execution and didn't print THE OUTPUT IS:0 like last time.

How to Raise Error Manually in SQL Server?

We can also raise errors manually at some required situations. It is used to return messages back to application using the same format as a system error or warning message is generated. For raising an error manually we use RAISERROR statement. RAISERROR can return either a user defined error message that has been created using the SP_ADDMESSAGE system stored procedure or a message string specified in the RAISERROR statement.

Syntax-  

RAISERROR (msg_id|msg_str|@local_variable, severity, State ,argument[,..n])

WITH option [,..n]

*msg_id is user defined error message number stored in the sys.messages catalog view. Using sp_addmessage system stored procedure user defined error message can be created in sys.messages table. It should be greater than 50000.When it is not specified RAISERROR raises an error message with an error number 50000.

*msg_str is a user defined message with formatting similar to the printf function in C.

*@local_variable is a variable of any valid charter data type that contains a string formatted in the same manner as msg_str. It must be char or varchar.

*Severity is the user defined severity level associated with this message. It can be 0-18.

* State is an arbitrary integer from 1-127.The value 0 and greater than 127 will raise an error.

Let’s understand this with an example

Problem - you don’t want to divide any number by 2. If anyone tries to divide with 2 it should throw error.

SQL Code-

CREATE PROCEDURE DIVISION_BY_TWO(@FIRST INT, @SECOND INT)

AS

BEGIN

Declare @RESULT int

BEGIN TRY

If @SECOND=2

RAISERROR('I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2',15,1)

SET @RESULT=@FIRST/@SECOND

PRINT 'The Output is: '+ Cast(@RESULT as varchar(2))

END TRY

BEGIN CATCH

PRINT Error_Message()

END CATCH

END

As you can from the above query in try block, if anyone tries to divide by 2 it raise error, catch block just after the try block catch gets the control and it prints the error message written in RAISERROR under TRY block.

Let’s try to divide 20 by 2.

EXEC DIVISION_BY_TWO 20, 2

As you can see from the above result, it raised an error which was raised manually. It printed the error message written in RAISEERROR under TRY block.

If you want to customize the error message with formatting then use the RAISERROR statement as following

RAISERROR ('We don’t want to divide %d with %d',15,1,@FIRST,@SECOND)

We can also use the WITH LOG option at the end of the string to write the error message into the SQL server Log file as following.

RAISERROR (‘We don’t want to divide %d with %d’,15,1,@FIRST,@SECOND)  WITH LOG

How to ADD USER Defined Error Messages in sys.messages catalog view?

 All the predefined error list of SQL server can be found in the SYS.Messages catalog view. Query on the database with the following statement where we can view the list of predefined errors.

SELECT * FROM SYS.MESSAGES

We can add new user defined error message in SYS.Messages using the system store procedure SP_AddMessage.

Syntax:

SP_AddMessage @msgid, @severity, @msgtext, @lang, @with_log, @replace

Example:

Exec SP_AddMessage 50001,16,'I AM RAISING THIS ERROR MANUALLY, SORRY YOU ARE NOT ALLOWED TO DIVIDE BY 2'

You can confirm that above error message has been added into sys.messages table this by running below query

SELECT * FROM SYS.MESSAGES WHERE TEXT LIKE 'I AM RAISING%'

I hope you enjoyed the article don't forget to share on Facebook.

Keep Learning!

Managing Transactions in Stored Procedure

In this article, I am giving a quick overview about Managing Transactions in Stored Procedure in SQL Server. After completing this article you will understand:

How to manage Transactions in Stored Procedure in SQL Server?

Please give your valuable suggestions and feedback to improve this article.

I have already discussed about Stored Procedure and Transactions in SQL Server in my previous articles, if you have no idea about Stored Procedure and Transactions then I will recommend you to visit the article.

I hope now you are comfortable with Stored Procedure and Transactions, we can continue this article.

I would like to give a problem before we understand Transactions in Stored Procedure.

Problem- Suppose You need to transfer some money into someone else account. How can you do this?

Solution- Yes, you are correct. You can write SQL query or stored procedure to deduct the money from the person’s account that is transferring and add the deducted money into the person’s account that is receiving the money.

SQL Code-

CREATE DATABASE TEACHMESQLSERVER

USE TEACHMESQLSERVER

GO

/*Creating BANKING Table*/

IF OBJECT_ID('BANKING', 'U') IS NOT NULL

DROP TABLE BANKING

CREATE TABLE BANKING (ACCOUNT INT, NAME VARCHAR(MAX), BALANCE MONEY)

GO

INSERT INTO BANKING VALUES(1,'GHANESH',50000)

INSERT INTO BANKING VALUES(2,'PRASAD',25000)

INSERT INTO BANKING VALUES(3,'YOGITA',35000)

INSERT INTO BANKING VALUES(4,'GAUTAM',4000)

GO

/*Inserted Records*/

SELECT * FROM BANKING

GO

/*Creating FUNDSTRANSFER Stored Procedure to transfer the money from one account to another account*/

CREATE PROCEDURE FUNDSTRANSFER (@SOURCEID INT, @DESTID INT, @AMT INT)

AS

BEGIN

UPDATE BANKING SET BALANCE = BALANCE -@AMT WHERE ACCOUNT=@SOURCEID

UPDATE BANKING SET BALANCE = BALANCE+@AMT WHERE ACCOUNT=@DESTID

END

GO

/*Checking the Balance before Transfering the money, then Transfering the money using Stored Procedure and checking the Balance after transfering*/

SELECT * FROM BANKING

EXEC FUNDSTRANSFER 2,3, 5000

SELECT * FROM BANKING

GO

As you can see from the above result set, 5000 amount has been transferred from PRASAD account to YOGITA account.

What do you think; is this enough?  My answer is Big No.

Suppose in above case if SourceID or DestID is not present then it will deduct the amount from the other account or add the amount from the other account.

As you know we have 4 accounts (1-4) in our BANKING Table, in below query I am transferring 5000 RS from account 1 to account 5. Please note account 5 is not present in the table. Let’s see what happens

Let’s execute the below code to understand the problem

SELECT * FROM BANKING

EXEC FUNDSTRANSFER 1,5, 5000

SELECT * FROM BANKING

GO

As you can see from the above result output 5000 Rs has been deducted from account 1 (GHANESH) but it was not added into any other account. It happened because we were transferring the amount into account 5 which is not present.

To overcome from the problem we need to use Transaction in Stored Procedure.

How to manage Transactions in Stored Procedure in SQL Server?

To manage the Transaction first we need to identify which statement is executed and which failed; for this we will use @@ROWCOUNT function.

Let’s ALTER our Stored Procedure.

ALTER PROCEDURE FUNDSTRANSFER(@SOURCEID INT, @DESTID INT, @AMT INT)

AS

BEGIN

DECLARE @COUNT1 INT, @COUNT2 INT

BEGIN TRANSACTION

UPDATE BANKING SET BALANCE=BALANCE-@AMT WHERE ACCOUNT=@SOURCEID

SET @COUNT1=@@ROWCOUNT

UPDATE BANKING SET BALANCE=BALANCE+@AMT WHERE ACCOUNT=@DESTID

SET @COUNT2=@@ROWCOUNT

IF @COUNT1=@COUNT2

BEGIN

COMMIT

PRINT 'AMOUNT HAS BEEN TRANFERRED'

END

ELSE

BEGIN 

ROLLBACK

PRINT 'AMOUNT TRANFERED FAILED'

END

END

Let’s try to transfer amount to account 5

EXEC FUNDSTRANSFER 1,5, 5000

As you can see this time, it didn't deduct the money. Transaction checks whether amount was deducted and added to the account, if not so then it fails.

I hope you enjoyed the article don't forget to share on Facebook.

Keep Learning!