User defined function in SQL Server

Written By:- Isha Malhotra
Our Website:-Tech Altum

User defined function in SQL Server

Functions are basically a separate area where we write our code and according to the need we can call it.

SQL Server allows you to create functions, as we create functions in programming language. We can pass parameter to function and function can also return the output. These are fast as these are precompiled.

Consider the following table prod_rep as an example:-

Figure 1

Type of User defined function in SQL Server

According to its return type we categorized the user defined function into following two parts

Scalar function

In scalar function, we can return a single value from the function.

For example:-

Suppose I have to create function which will return the dept according to id.

Rank Function in SQL server

Written By:- Isha Malhotra (Software Developer)
Email:-malhotra.isha3388@gmail.com.
Note: -if you find this article helpful then kindly leave your comment

Rank Function in SQL server

SQL server also supports Some rank function also. To use the rank function you must have to assign it to Over function. In over function we give the order in which records will be Shown.

These functions are Following:-

   1.    Rank ()

This function is used to Define the rank according to the order which we set in order by clause.

For example:-

See the following records Of temp table:-

In this table there are Two records which having the same salary i.e. 1000.

Now apply the rank function on it.

Use of String function in Sql Server

Use of String Function in SQL Server

SQL Server support string function which is Already defined. Following are some important string function and their uses:-

       1.       Char (ASCII code)

This function returns the The character which is equivalent to particular ASCII code which you will pass too This function.

For Example:-

Select char (66);

This function will Return B.

      2.       ASCII (Char)

This function Returns the ASCII code which is equivalent to particular char which you will Pass to this function.

For example:-

Select ASCII ('B');

this function will return 66

     3.       Len(char)

This function returns the length of string which you will pass to len function. It returns integer value.

For Example:-

select LEN('isha malhotra');

this function will return 13

  4.  Left(char, number of char)

This function will returns the number of char from left.

For example:-

select LEFT('isha malhotra',3);

this function will return ish.