Functions in SQL Server


Functions in SQL Server
In SQL Server functions are subrotienes that encapsulate a group of T-SQL statements for reuse.SQL Server provides Scaler functions,table valued functions,and built in functions.You can also defines your own function using CREATE FUNCTION statement.Now brief about different type of functions.

Scaler Functions:
Scaler funtions return a single value.The return value can be any data type except text,Image,cursor and timestamp.Scaler functions can be inline or  multistaement functions.In inline functions you do not need to use BEGIN-END(means there is no function body).

Synatx:

CREATE FUNCTION FunctionName (InputParameters)
RETURNS DataType
AS
BEGIN
--Code
RETURN Expression
END

How to call scaler valued functions?

*       SELECT FunctionName(InputParameters)

Example :
Let me show an example that returns day of month with suffix.

CREATE FUNCTION [dbo].[fnGetSuffix]
(
@Day varchar(2) --INPUT Parameter
)

returns Varchar(4)  --What type you want to return
as

BEGIN
declare @monthDays Varchar(4)
           
              IF @Day = '1' Or @Day = '11' OR @Day = '21' OR @Day= '31'
           
        BEGIN
            Set @monthDays = @Day + 'st';
        END
       
        ELSE IF (@Day = '2' OR @Day = '22')
       
        BEGIN
            Set @monthDays = @Day + 'nd';
        END
       
        ELSE IF (@Day = '3' OR @Day = '23')
       
        BEGIN
            Set @monthDays = @Day + 'rd';
        END
       
        ELSE
       
        BEGIN
            Set @monthDays = @Day + 'th';
        END

return @monthDays
           
END

Exceute above T-SQL Staements to create fnGetSuffix function.Now
You can use UDF fnGetSuffix.
*       select dbo. fnGetSuffix (5);

Output:
5th





Table Valued Functions:
Table valued functions return a table data type instead of single value.Table valued functions also can be inline or multistatements functions.
Syntax for Inline Table Valued function:


CREATE FUNCTION FunctionName (InputParameters)
RETURNS Table
AS

RETURN (Select Statement)

Syntax for MultiStatement Table Valued function:

CREATE FUNCTION FunctionName (list of InputParamenters sepaerated by comma)
RETURNS @TableName TABLE (List of Columns sepaerated by comma)
AS
BEGIN
--Code to fill table variable
RETURN
END

How to call Table valued functions?

Since table valued functions return a table, you can use select statement as on table.
Synatx:

*      Select *from dbo.FunctionName(pass list of required parameters)

However you can also use :: to call Built in Table value function.
As
*      select *from ::fn_helpcollations()

or

*       select *from fn_helpcollations()

both of above give same results.

Exaple:You can create a table valued function to split a string by a given delimeter as

CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
 
    RETURN 
 
END

Exceute this T-sql statements to create function and use as

·         select *from dbo.fnSplitString('Querying SQL Server','')

Output:














Built-In functions:
SQL Server provide a wide varity of built in functions as SUM(),GETDATE(),AVG() etc to perform various tasks.You can not modify built in functions.Bulit in functions can be either scaler valued or table valued.

Example:

Built in functions can be categorise as
Aggregate Functions
Operate on collection of values and return a single summarised value.as AVG(),SUM(),COUNT() etc.
Rowset  functions
return an object that can be used as table like OPENQUERY(),OPENDATASOURCE(),OPENROWSET(),OPENXML().
Ranking functions
Return a ranking value for each row in a partition
 RANK(),DENSE_RANK(),ROW_NUMBER().
Scalar Functions
Operate  on single value and then return a single value like CAST(),CONVERT(),ABS(),EXP(),POWER() etc.


Aggregate Functions:

select AVG(salary) from Person --22254444.444444

select SUM(salary) from Person –200290000

select MAX(salary) from Person –100000000

select MIN(salary) from Person –20000

select COUNT(*) from Person    --9

select COUNT_BIG(salary) from Person --9 [work as count only diffrence COUNT_BIG always returns a bigint data type value]

select STDEV(salary) from Person --44077593.8292663 Returns the statistical standard deviation of all values in the specified expression

select CHECKSUM(*) from Person --return checksum value computed over a row of a table

Rowset Functions:

·         OPENDATASOURCE ( provider_name, init_string ): make a connection with other databases.

Ranking Functions:
·         RANK ( ) OVER ( [ partition_by_clause ] order_by_clause ) : it returns rank of earch row within partition of resultset.


Scalar functions:
·         Mathematical Functions

select ABS(-10.233) --10.233 return positive value

select ABS(2-10)  --8

select ACOS(-1.0) --3.14159 returns the angle, in radians, whose cosine is the specified float expression

select CEILING(10.2) --11 returns the smallest integer greater than/eqaul to specified  expression.

select FLOOR(10.2) --10 returns the largest integer smaller than/eqaul to specified  expression.

select EXP(1.0) --2.71828182845905 returns the exponential value of the specified float expression[e^1.0 = 2.71828182845905].

select LOG(10)  --2.30258509299405

select LOG10(10) –1

select POWER(10,2) –100

select PI() --3.14159265358979

select RAND(100), RAND(), RAND()  --Returns a pseudo-random float value from 0 through 1

Synatx : ROUND ( numeric_expression , length [ ,function ] )

select ROUND(10.22353,1) --Returns a numeric expression ,rounded to the specified length ,precision
select ROUND(168.58, -1) --170.00
select ROUND(168.58,-2)  --200.00
select ROUND(168.58,1)   --168.60

select SQRT(4) -- 2 return square root of specified expression

select SQUARE(4) --16 return square of specified expression


·            Date and Time Functions:

--GETDATE()=09/22/2012
select SYSDATETIME () --2012-09-22 15:54:57.0698177  return system datetime of type datetime2(7)

select SYSDATETIMEOFFSET ( )--2012-09-22 15:55:52.4441089 +05:30 also inclued time offset

select SYSUTCDATETIME ( )  --2012-09-22 10:26:48.6308897 The date and time is returned as UTC time (Coordinated Universal Time).

select CURRENT_TIMESTAMP   --2012-09-22 15:59:56.987 return current timestamp.The time zone offset is not included.

select GETDATE()   --2012-09-22 16:00:55.733 current time of system.The time zone offset is not included.

select GETUTCDATE ( ) --2012-09-22 10:32:14.667 return UTC datetime

--DATENAME(datepart,date) datepart can be day,month ,year,hour etc [returns nvarchar]
select DATENAME(DAY,GETDATE()) --22
select DATENAME(MONTH,CAST('05/22/2012' as DATETIME)) –MAY

--DATEPART ( datepart , date ) [returns int]
select DATEPART(MONTH,GETDATE()) --9
select DATEPART(YEAR,GETDATE())  --2012

--Day(),MONTH(),YEAR() [all returns int]
select DAY(GETDATE()) --22
Select MONTH(GETDATE()) --9
select YEAR(GETDATE())  --2012

--DATEDIFF ( datepart , startdate , enddate ) Returns the int.
select DATEDIFF (DAY,getdate(),CAST('10/22/2012' as DATETIME)) --30 day
select DATEDIFF (MONTH,getdate(),CAST('10/22/2012' as DATETIME)) --1 month
select DATEDIFF (week,getdate(),CAST('10/22/2012' as DATETIME)) --5 week

--ISDATE ( expression ) to validate datetime [return int]
select ISDATE('queryingSQL') --0
select ISDATE('09/22/2012')  --1

·         String functions:

--ASCII() Returns the ASCII code value of the leftmost character of a character expression.
select ASCII('queryingsql') --113
select ASCII('q')      --113

--CHAR() :convert ASCII code to char
select CHAR(113) –q

--CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )  return int
select CHARINDEX('SQL','querying SQL') –10

--LEFT() :Returns the left part of a character string with the specified number of characters.
select LEFT('querying sql',10) --querying s

--RIGHT() : Returns the right part of a character string with the specified number of characters.
select RIGHT('querying sql',10) --erying sql

--LEN(): return length of string excluding trailing spaces
select LEN('querying sql') --12

select UPPER('querying sql') --QUERYING SQL
select LOWER('QUERYING sql') --querying sql

--LTRIM(): Returns a character expression after it removes leading blanks.
select LTRIM('   querying SQL') --querying SQL

--RTRIM(): Returns a character expression after it removes trailing blanks.
select RTRIM('querying   SQL    ') --querying   SQL

SELECT REVERSE('queryingSQL') --LQSgniyreuq

--REPLICATE ( string_expression ,integer_expression ) :Repeats a string value a specified number of times.
select REPLICATE('querying SQL',3) --querying SQLquerying SQLquerying SQL


--STUFF ( character_expression , start , length , replaceWith_expression ):inserts a string into another string.
SELECT STUFF('queryingSERVER', 9, 3, 'SQL'); --queryingSQLVER
 SELECT STUFF('queryingSERVER', 9, 6, 'SQL'); --queryingSQL [--SERVER replaced by SQL.]

 --SUBSTRING ( expression ,start , length ) : Returns part of a character, binary, text, or image expression
 Select SUBSTRING('queryingSQL',9,3) --SQL

 --PATINDEX ( '%pattern%' , expression ):Returns the starting position of the first occurrence of a pattern in a specified expression

SELECT PATINDEX('%sql%','querying SQL') --10
SELECT PATINDEX('%s_l%','querying SQL') --10

--UNICODE(): return unicode integer value of first character expression
SELECT UNICODE('queryingSQL') –113


Reference


Important Points for User Defined Functions:

1.    You can not use TRY-CATCH block with user defined functions.
2.    You can use UDF any where in SQL Statements.
3.    You can not call stored procedure from UDF.





Prev--->Error Handling in T-SQL                                                     Next--->Basics of Triggers in T-SQL



2 comments:

Please leave a comment for this post

Subscribe to: Posts (Atom)