User Defined Function in Sql-Server

Today i want to discuss an article that is regarding User Defined Functions in Sql-server.What are User Defined Functions?what are the types of User Defined Functions.In which scenario User Defined Functions are Used.This is the article i want to discuss here with Code Snippets.

User Defined functions are the functions that are Created by the User to solve a problem for specific Scenarios.There are two types of User Defined functions one is Scalar and another one is tabular valued function.

Scalar Valued User Defined Functions :

Scalar Valued Functions returns Integer,Varchar datatypes It will not return a table and specific Datatypes like blob e.t.c.

One table column Data contains like this.


we need to show the output like this


for that i use a Scalar valued user defined function to solve this problem now see the below Code with images.

First i will describe the step by step description of the code.

Step :1

Send Column Name and its datatype as a Function arguments and specify the Function return type. In this below function it is varchar here


Now Find '-' Symbol in the Column value with the help of patindex function this function will search entire string if such First match found that Index position it returns as integer works same as charindex Function.

step :3
Now substring the string value or column value from the return value(resultant string) from patindex +1(Index of the string) upto length of the string.

step :4

Repeat step2 from the Substring result.

step :5

Again Substring from starting index of the string to patindex('-' Symbol of the position found) with -1


Return the Required output.

ALTER function dblmatrix(@Column varchar(30)) returns varchar(50)
Declare @findex int
Declare @lfindex int
Declare @Beginse varchar(50)
Declare @lastse varchar(50)
Declare @lasts int
Declare @BeginCount int
declare @lngtrema int
set @findex = patindex(‘%-%',@Column)
set @Beginse = substring(@Column,@findex+1,len(@Column))
set @lfindex = patindex(‘%-%',@Beginse)
set @Beginse = substring(@Beginse,1,@lfindex-1)
return @Beginse

select dbo.dblmatrix(alpha) from tablename

User Defined function sql

now look at the output before executing the query


see the query.. after this query

User Defined query

Now the Output...

Out put query

Tabular valued Function

A Tabular valued function returns a table after executing the function.look at the below function how it returns a table.

Tabular valued Function

CREATE FUNCTION [dbo].[GetCommentsOfProduct]
@ProductId int
SELECT * FROM ProductComments WHERE ProductId = @ProductId

Return table query

Now look at the code........

GetUserDefined query

Now the output query after executing our function


Tabular valued Function

Note: If the Function is in Another database and it is on same server we can Use that User Defined Function from the below Code.Suppose if the Function is on another Database Name admin

select * from dbo.Admin.dblmatrix(alpha) from tablename


Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 74 articles authored by srirama


Author: sudhanshu pal30 Jan 2014 Member Level: Silver   Points : 2

Hello All,

You can make an userdefined function like this:
create function get_code()
returns char(10)
as begin declare @lastval char(5)
set @lastval = (select max(advisor_code) from tbl_advisor_table)
if @lastval is null set @lastval = 'DFP'
declare @i int set @i = right(@lastval,4) + 1
return 'C' + right('000' + convert(varchar(10),@i),4)


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: