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.

1234-sheeba-2345
1001234567-Projectname-(1234567)

we need to show the output like this

sheeba
Projectname

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

Step:2

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

Step:6

Return the Required output.



ALTER function dblmatrix(@Column varchar(30)) returns varchar(50)
as
Begin
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
End

select dbo.dblmatrix(alpha) from tablename



User Defined function sql


now look at the output before executing the query

Dataoutputvalues

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
)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM ProductComments WHERE ProductId = @ProductId
)


Return table query

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

GetUserDefined query

Now the output query after executing our function

Outputquerys

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


Attachments

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 73 articles authored by srirama

Comments

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)

end



  • 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:
    Email: