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
now look at the output before executing the query
see the query.. after this query
Now the Output...
Tabular valued Function
A Tabular valued function returns a table after executing the function.look at the below function how it returns a table.
CREATE FUNCTION [dbo].[GetCommentsOfProduct]
(
@ProductId int
)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM ProductComments WHERE ProductId = @ProductId
)
Now look at the code........
Now the output query after executing our 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
Follow
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