SQL Server Functions Tutorial
Check out Best Alternative to InfoPath -> Try Now
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
SharePoint 2016 Tutorial Contents
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:
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.
identity (seed, increment)
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.
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:
- SQL Server stored procedure tutorial
- Difference between clustered and non clustered index SQL server
- Difference between primary key and unique key in SQL Server
- Difference between primary key and foreign key in SQL server
- Delete and create a new log file with minimum size in SQL Server
- Enable remote connections for SQL Server
- Sequence in SQL server
- SQL Server joins tutorial
- SQL DML and DDL commands
- How to Setting maximum memory in SQL server?
- Create Configure and Use SQL Server Database using Microsoft Azure
- How to create SQL Database in Microsoft Azure Portal
- Connect to Azure SQL database from management studio
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.
Download SharePoint Online Tutorial PDF FREE!
Get update on Webinars, video tutorials, training courses etc.