Function in SQL Server with Examples
Code: Use this below code to select a specific database
<span class="pln"> USE </span><span class="typ">TestDatabase</span><span class="pln"> </span>
Code: Create this below table to understand function
<span class="pln"> CREATE TABLE </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].[</span><span class="typ">Emp_Master</span><span class="pun">](</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Emp_ID</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">nchar</span><span class="pun">](</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Emp_Name</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">nchar</span><span class="pun">](</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Emp_DOB</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">date</span><span class="pun">]</span><span class="pln"> NULL </span><span class="pun">)</span><span class="pln"> ON </span><span class="pun">[</span><span class="pln">PRIMARY</span><span class="pun">]</span><span class="pln"> </span>
Code: Create this below table
<span class="pln"> CREATE TABLE </span><span class="pun">[</span><span class="pln">dbo</span><span class="pun">].[</span><span class="typ">EmpSalary</span><span class="pun">](</span><span class="pln"> </span><span class="pun">[</span><span class="pln">ID</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">nchar</span><span class="pun">](</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Name</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">nchar</span><span class="pun">](</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Salary</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">numeric</span><span class="pun">](</span><span class="lit">10</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">)</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="pun">[</span><span class="typ">Dept</span><span class="pun">]</span><span class="pln"> </span><span class="pun">[</span><span class="pln">nchar</span><span class="pun">](</span><span class="lit">10</span><span class="pun">)</span><span class="pln"> NULL </span><span class="pun">)</span><span class="pln"> ON </span><span class="pun">[</span><span class="pln">PRIMARY</span><span class="pun">]</span><span class="pln"> </span>
Insert some records inside above two table.
Code: use this code to see two table
<span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">EmpSalary</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Emp_Master</span><span class="pln"> ALTER TABLE </span><span class="typ">Emp_Master</span><span class="pln"> ADD </span><span class="typ">Emp_DOB</span><span class="pln"> DATE</span><span class="pun">;</span><span class="pln"> UPDATE </span><span class="typ">Emp_Master</span><span class="pln"> SET </span><span class="typ">Emp_DOB</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'12-02-1996'</span><span class="pln"> WHERE </span><span class="typ">Emp_ID</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">1</span><span class="pln"> UPDATE </span><span class="typ">Emp_Master</span><span class="pln"> SET </span><span class="typ">Emp_DOB</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'11-02-1996'</span><span class="pln"> WHERE </span><span class="typ">Emp_ID</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">2</span><span class="pln"> UPDATE </span><span class="typ">Emp_Master</span><span class="pln"> SET </span><span class="typ">Emp_DOB</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'10-02-1996'</span><span class="pln"> WHERE </span><span class="typ">Emp_ID</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">3</span><span class="pln"> UPDATE </span><span class="typ">Emp_Master</span><span class="pln"> SET </span><span class="typ">Emp_DOB</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'09-02-1996'</span><span class="pln"> WHERE </span><span class="typ">Emp_ID</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">4</span><span class="pln"> UPDATE </span><span class="typ">Emp_Master</span><span class="pln"> SET </span><span class="typ">Emp_DOB</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="str">'08-02-1996'</span><span class="pln"> WHERE </span><span class="typ">Emp_ID</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">5</span><span class="pln"> </span>
Code: Create a simple function using this below code
<span class="pln"> </span><span class="com">/* User Defined Function */</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Tabled</span><span class="pln"> </span><span class="typ">Valued</span><span class="pln"> </span><span class="typ">Function</span><span class="pln"> CREATE FUNCTION </span><span class="typ">SelectDataEmpSalary</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@ID</span><span class="pln"> INT </span><span class="pun">)</span><span class="pln"> RETURNS TABLE AS RETURN SELECT </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Salary</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Dept</span><span class="pln"> FROM </span><span class="typ">EmpSalary</span><span class="pln"> WHERE ID </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@ID</span><span class="pun">;</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Execute</span><span class="pln"> </span><span class="typ">Function</span><span class="pln"> SELECT </span><span class="pun">*</span><span class="pln"> FROM </span><span class="typ">SelectDataEmpSalary</span><span class="pun">(</span><span class="lit">1</span><span class="pun">)</span><span class="pln"> </span>
Code: Use this below code to create a function
<span class="pln"> </span><span class="com">/* Scalar Valued Function 1 */</span><span class="pln"> CREATE FUNCTION </span><span class="typ">CubeFunction</span><span class="pun">(</span><span class="lit">@X</span><span class="pln"> INT</span><span class="pun">)</span><span class="pln"> RETURNS INT AS </span><span class="kwd">BEGIN</span><span class="pln"> RETURN </span><span class="lit">@X</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="lit">@X</span><span class="pln"> </span><span class="pun">*</span><span class="lit">@X</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Execute</span><span class="pln"> SELECT dbo</span><span class="pun">.</span><span class="typ">CubeFunction</span><span class="pun">(</span><span class="lit">5</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">Cube</span><span class="pln"> </span>
Code: Example of Scalar Valued function
<span class="pln"> </span><span class="com">/* Scalar Valued Function 2 */</span><span class="pln"> CREATE FUNCTION </span><span class="typ">Addition</span><span class="pun">(</span><span class="lit">@Num1</span><span class="pln"> </span><span class="typ">Decimal</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="lit">2</span><span class="pun">),</span><span class="pln"> </span><span class="lit">@Num2</span><span class="pln"> </span><span class="typ">Decimal</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="lit">2</span><span class="pun">))</span><span class="pln"> RETURNS </span><span class="typ">Decimal</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="lit">2</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Begin</span><span class="pln"> DECLARE </span><span class="lit">@Result</span><span class="pln"> </span><span class="typ">Decimal</span><span class="pun">(</span><span class="lit">7</span><span class="pun">,</span><span class="lit">2</span><span class="pun">)</span><span class="pln"> SET </span><span class="lit">@Result</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> </span><span class="lit">@Num1</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="lit">@Num2</span><span class="pln"> RETURN </span><span class="lit">@Result</span><span class="pln"> </span><span class="kwd">end</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Execute</span><span class="pln"> </span><span class="kwd">print</span><span class="pln"> dbo</span><span class="pun">.</span><span class="typ">Addition</span><span class="pun">(</span><span class="lit">12</span><span class="pun">,</span><span class="lit">13</span><span class="pun">)</span><span class="pln"> </span>
Code: Example of Scalar Valued function
<span class="pln"> </span><span class="com">/* Scalar Valued Function 3 */</span><span class="pln"> CREATE FUNCTION </span><span class="typ">CalculateAge</span><span class="pln"> </span><span class="pun">(</span><span class="pln"> </span><span class="lit">@DOB</span><span class="pln"> DATE </span><span class="pun">)</span><span class="pln"> RETURNS INT AS </span><span class="kwd">BEGIN</span><span class="pln"> DECLARE </span><span class="lit">@AGE</span><span class="pln"> INT SET </span><span class="lit">@AGE</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> DATEDIFF</span><span class="pun">(</span><span class="pln">YEAR</span><span class="pun">,</span><span class="pln"> </span><span class="lit">@DOB</span><span class="pun">,</span><span class="pln"> GETDATE</span><span class="pun">())-</span><span class="pln"> CASE WHEN </span><span class="pun">(</span><span class="pln">MONTH</span><span class="pun">(</span><span class="lit">@DOB</span><span class="pun">)</span><span class="pln"> </span><span class="pun">></span><span class="pln"> MONTH</span><span class="pun">(</span><span class="pln">GETDATE</span><span class="pun">()))</span><span class="pln"> OR </span><span class="pun">(</span><span class="pln">MONTH</span><span class="pun">(</span><span class="lit">@DOB</span><span class="pun">)</span><span class="pln"> </span><span class="pun">=</span><span class="pln"> MONTH</span><span class="pun">(</span><span class="pln">GETDATE</span><span class="pun">())</span><span class="pln"> AND DAY</span><span class="pun">(</span><span class="lit">@DOB</span><span class="pun">)</span><span class="pln"> </span><span class="pun">></span><span class="pln"> DAY</span><span class="pun">(</span><span class="pln">GETDATE</span><span class="pun">()))</span><span class="pln"> THEN </span><span class="lit">1</span><span class="pln"> ELSE </span><span class="lit">0</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> RETURN </span><span class="lit">@AGE</span><span class="pln"> </span><span class="kwd">END</span><span class="pln"> </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Execute</span><span class="pln"> SELECT dbo</span><span class="pun">.</span><span class="typ">CalculateAge</span><span class="pun">(</span><span class="str">'12-02-1996'</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> age </span><span class="pun">--</span><span class="pln"> </span><span class="typ">Use</span><span class="pln"> the above </span><span class="kwd">function</span><span class="pln"> </span><span class="kwd">in</span><span class="pln"> a </span><span class="typ">Query</span><span class="pln"> SELECT </span><span class="typ">Emp_ID</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Emp_Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Emp_DOB</span><span class="pun">,</span><span class="pln"> dbo</span><span class="pun">.</span><span class="typ">CalculateAge</span><span class="pun">(</span><span class="typ">Emp_DOB</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">Age</span><span class="pln"> FROM </span><span class="typ">Emp_Master</span><span class="pln"> </span>
Code: Example of Scalar Valued function
<span class="pln"> </span><span class="com">/* Drop Function */</span><span class="pln"> DROP FUNCTION </span><span class="typ">FuncationName</span><span class="pln"> </span>
Code: Example of Scalar Valued function
<span class="pln"> </span><span class="com">/* Advanced Functions */</span><span class="pln"> SELECT USER_NAME</span><span class="pun">();</span><span class="pln"> SELECT SYSTEM_USER</span><span class="pun">;</span><span class="pln"> SELECT SESSION_USER</span><span class="pun">;</span><span class="pln"> SELECT SESSIONPROPERTY</span><span class="pun">(</span><span class="str">'ANSI_NULLS'</span><span class="pun">);</span><span class="pln"> SELECT ISNUMERIC</span><span class="pun">(</span><span class="lit">4567</span><span class="pun">);</span><span class="pln"> SELECT NULLIF</span><span class="pun">(</span><span class="lit">25</span><span class="pun">,</span><span class="pln"> </span><span class="lit">25</span><span class="pun">);</span><span class="pln"> SELECT NULLIF</span><span class="pun">(</span><span class="str">'Hello'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Hello'</span><span class="pun">);</span><span class="pln"> SELECT NULLIF</span><span class="pun">(</span><span class="str">'Hello'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'world'</span><span class="pun">);</span><span class="pln"> SELECT NULLIF</span><span class="pun">(</span><span class="str">'2017-08-25'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2017-08-25'</span><span class="pun">);</span><span class="pln"> SELECT ISNULL</span><span class="pun">(</span><span class="pln">NULL</span><span class="pun">,</span><span class="pln"> </span><span class="str">'RummanAnsai'</span><span class="pun">);</span><span class="pln"> SELECT IIF</span><span class="pun">(</span><span class="lit">500</span><span class="pun"><</span><span class="lit">1000</span><span class="pun">,</span><span class="pln"> </span><span class="str">'YES'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'NO'</span><span class="pun">);</span><span class="pln"> SELECT CONVERT</span><span class="pun">(</span><span class="kwd">int</span><span class="pun">,</span><span class="pln"> </span><span class="lit">25.65</span><span class="pun">);</span><span class="pln"> SELECT COALESCE</span><span class="pun">(</span><span class="pln">NULL</span><span class="pun">,</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rumman'</span><span class="pun">,</span><span class="pln"> NULL</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Ansari'</span><span class="pun">);</span><span class="pln"> SELECT COALESCE</span><span class="pun">(</span><span class="pln">NULL</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="lit">2</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rumman'</span><span class="pun">);</span><span class="pln"> SELECT CAST</span><span class="pun">(</span><span class="lit">25.65</span><span class="pln"> AS </span><span class="kwd">int</span><span class="pun">);</span><span class="pln"> </span><span class="com">/* Date Functions */</span><span class="pln"> SELECT CURRENT_TIMESTAMP</span><span class="pun">;</span><span class="pln"> SELECT DATEADD</span><span class="pun">(</span><span class="pln">year</span><span class="pun">,</span><span class="pln"> </span><span class="lit">1</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2017/08/25'</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DateAdd</span><span class="pun">;</span><span class="pln"> </span><span class="com">/* Add one year to a date, then return the date:*/</span><span class="pln"> SELECT DATEDIFF</span><span class="pun">(</span><span class="pln">year</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2017/08/25'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2011/08/25'</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DateDiff</span><span class="pun">;</span><span class="pln"> </span><span class="com">/*Return the difference between two date values, in years:*/</span><span class="pln"> SELECT DATEFROMPARTS</span><span class="pun">(</span><span class="lit">2018</span><span class="pun">,</span><span class="pln"> </span><span class="lit">10</span><span class="pun">,</span><span class="pln"> </span><span class="lit">31</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DateFromParts</span><span class="pun">;</span><span class="pln"> </span><span class="com">/*Return a date from it's parts:*/</span><span class="pln"> SELECT DATENAME</span><span class="pun">(</span><span class="pln">year</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2017/08/25'</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DatePartString</span><span class="pun">;</span><span class="pln"> </span><span class="com">/* Return a specified part of a date: */</span><span class="pln"> SELECT DATEPART</span><span class="pun">(</span><span class="pln">year</span><span class="pun">,</span><span class="pln"> </span><span class="str">'2017/08/25'</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DatePartInt</span><span class="pun">;</span><span class="pln"> </span><span class="com">/*Return a specified part of a date:*/</span><span class="pln"> SELECT DAY</span><span class="pun">(</span><span class="str">'2017/08/25'</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">DayOfMonth</span><span class="pun">;</span><span class="pln"> </span><span class="com">/* Return the day of the month for a date: */</span><span class="pln"> SELECT GETDATE</span><span class="pun">();</span><span class="pln"> </span><span class="com">/* Return the current database system date and time: */</span><span class="pln"> SELECT GETUTCDATE</span><span class="pun">();</span><span class="pln"> </span><span class="com">/* Return the current UTC date and time: */</span><span class="pln"> SELECT ISDATE</span><span class="pun">(</span><span class="str">'2017-08-25'</span><span class="pun">);</span><span class="pln"> </span><span class="com">/* Check if the expression is a valid date: */</span><span class="pln"> SELECT MONTH</span><span class="pun">(</span><span class="str">'2017/08/25'</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">Month</span><span class="pun">;</span><span class="pln"> </span><span class="com">/* Return the month part of a date: */</span><span class="pln"> SELECT SYSDATETIME</span><span class="pun">()</span><span class="pln"> AS </span><span class="typ">SysDateTime</span><span class="pun">;</span><span class="pln"> </span><span class="com">/* Return the date and time of the SQL Server: */</span><span class="pln"> SELECT YEAR</span><span class="pun">(</span><span class="str">'2017/08/25'</span><span class="pun">)</span><span class="pln"> AS </span><span class="typ">Year</span><span class="pun">;</span><span class="pln"> </span><span class="com">/* Return the year part of a date: */</span><span class="pln"> </span>
