SQL Server Functions Tutorial

This SQL Server tutorial, we will discuss SQL Server functions. We will discuss below things:

  • Error functions in sql server
  • User Defined Function example in SQL Server
  • How to return multiple values from a function in sql server
  • Various Date Formats in SQL server
  • Identity column and Identity functions in sql server

Error functions in sql server

Now, we will discuss various error functions in sql server.

Sql server provides a set of functions to get information about raised errors which are known as Error Functions.

Within the scope of a TRY CATCH block, you can use the following error functions:

  • ERROR_NUMBER(): Every error in sql server is associated with a unique number that is uniquely identified. This function returns the error number of the currently raised error in your program.
  • ERROR_STATE(): Every error in sql server is associated with a state which can be used to identify the exact location in the program where that error occurs. This function will return the state of the raised error.
  • ERROR_PROCEDURE(): This function returns the name of the stored procedure or trigger in which error occurs. This function returns NULL if the error did not occur inside a stored procedure or trigger.
  • ERROR_MESSAGE(): This function is used to get the message related to the raised error in the program. This function returns the text of the message that would be returned to the application. The text includes the values supplied for any substitutable parameters such as names, length or time.
  • ERROR_LINE(): This function is used to get the line number within the stored subprogram where the error occurs.
  • ERROR_SEVERITY(): Every error in sql server has the severity of how severe the error is. This function will return the severity of the currently raised error in the program.

Immediately after executing any TSQL statement, you can test for an error and retrieve that error number using the @@ERROR function.

Getting a list of error messages in sql server

By using a system table called sys.messages you can get the complete list of error messages available in the sql server to which you are connected.

Ex: Select * from sys.messages

By using the stored procedure named sp_addmessage you can add your own messages to the built in error messages of sql server.

Ex. Sp_addmessage msgid, severity, msgtext

User Defined Function example in SQL Server

Now, we will see what are user defined Function in sql server. We will see user defined functions can be created by users.

Below is a function that will take one input parameter and also returns one integer value.

Here it will take the empid and returns the contact number for the particular empid.

Create function EmployeeContact(@Empid int)
returns int
as
begin
declare @result int
select @result=EmloyeeDetail.ContactNo from EmloyeeDetail where employee detail.Empid=@Empid
return @result
end

You can call the function like below

Select dbo.EmployeeContact(1) as ContactNumber from EmloyeeDetail

This will return the contact number of empid =1.

Here are some points to remember about functions:

  • The function returns only a single value.
  • The function accepts only input parameters.
  • The function can not be used to Insert, Update, Delete data in database table.
  • The function doesn’t support Exception handling.

How to return multiple values from a function in sql server

Now, let us discuss how to return multiple values from an SQL server function in SQL server 2012. Here my requirement is I want to return multiple values through function.

In this case, we have to define a Table-valued function. Below is the syntax.

ALTER function [dbo].[fnGetTags]
(
@DiscussionID BigInt
)
RETURNS TABLE
AS
RETURN (SELECT TagID,TagName FROM Tags WHERE TAGID in (Select Top 1 TagID from DiscussionTags where DiscussionID= @DiscussionID))

To call this function you can use like below:

SELECT * FROM [dbo].fnGetTags

Various Date Formats in SQL server

Now, let us discuss various date formats in sql server.

By default if GETDATE() function returns like this, 2013-11-24 14:37:59.463.

By if you want you can format GETDATE () result like below:

Various formats are:

Select CONVERT(VarChar, GETDATE(), 120) As NewDateFormat
Select CONVERT(VarChar, GETDATE(), 121) As NewDateFormat
Select CONVERT(VarChar, GETDATE(), 126) As NewDateFormat
Select CONVERT(VarChar, GETDATE(), 127) As NewDateFormat
Select CONVERT(VarChar, GETDATE(), 130) As NewDateFormat
Select CONVERT(VarChar, GETDATE(), 131) As NewDateFormat

If you run above queries, the result will appear like below:

Date Formats in SQL server

Identity column and Identity functions in sql server

Now, we will discuss identity column and identity functions in sql server.

Identity is used to generate values automatically for a column during insert. While specifying identity you have to provide two values related to identity i.e, seed and increments. ‘seed’ is used to specify the first value to be generated by the identity and ‘increment’ is used to specify how much increment to be done to the previous value of the identity to generate the new value.

Syntax:

identity (seed, increment)

Ex:

identity (100,2)

Within a table, you can specify identity only on one column. While inserting rows into that table if there is identity column in the table, you must exclude the identity column and for this, you must use the below insert command syntax.

The following example creates a table with the name Employee with identity on Empid column.

Create table Employee
(Empid int primary key identity(101,1),
Ename varchar(30) not null)

Identity Functions in SQL Server

SQL Server provides a set of functions called Identity functions to get information about identity available on a table.

They are as follows:

  • Ident_seed(“tablename”): Used to get seed value of identity available in the given table.
  • Ident_incr(“tablename”): Used to get increment value of identity available in the given table.
  • Ident_current(“tablename”): Used to get current value of identity i.e, the last value generated by the identity.
  • @@identity: Used to get identity value generated by the last insert statement. If the last insert statement does not generate an identity value then this variable will contain null.

Inserting values for identity column explicitly

By default, you can’t provide value for identity column explicitly. If you want to insert values for identity column explicitly you need to set ‘identity _insert’ option ‘on’ for the identity table.

Syntax:

set identity _insert on/off

You can set this option to “on” for only one table at a time and when you set this option to ‘off’ after explicitly inserting values for the identity column, then the last value you inserted for the identity column will automatically become the current value of identity.

Specifying identity in GUI in sql server

To specify identity on a column while creating the table in GUI, first you need to click on the column, then go to column properties available at the bottom, expand the property “identity specifications”, set the property ‘is identity’ to ‘yes’ and then specify identity seed and identity increment.

You may like following SQL Server Tutorials:

Here, we learned SQL Server functions, Error functions in sql server, User Defined Function example in SQL Server, how to return multiple values from a function in sql server, Identity column and Identity functions in sql server.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

Bijay Kumar

I am Bijay from Odisha, India. Currently working in my own venture TSInfo Technologies in Bangalore, India. I am Microsoft Office Servers and Services (SharePoint) MVP (5 times). I works in SharePoint 2016/2013/2010, SharePoint Online Office 365 etc. Check out My MVP Profile.. I also run popular SharePoint web site SPGuides.com

>