Function In SQL Server Part VIII

Function IN SQL Server

Function :
  • A function is a set of SQL statements that perform a specific task. 
  • Functions foster code reusability.
There are Two Type of Function :

A) System Defined Function :
  • System functions are built-in database functions. 
There are two types of system-defined functions in SQL Server :

1) System Scalar Functions :
  • Scalar are on single value and returns single value.
2) System Aggregate Functions :
  • Aggregates the values and return a single value.
B) User-Defined Function :
  • User-defined functions are create by a user.
There are three types of user-defined functions in SQL Server :

Table :
tblCustomer 

1) Scalar Function :
  • It is a function that takes one or more values but returns a single value.
Steps :

1) Select Function then Right click on Function and select new Scalar-valued Function in Programmability : 


2) Example - Create Scalar-valued Function :


3) Scalar-valued Function Created Successfully :

4) Output :

2) Inline Table-Valued Function :
  • It returns a table variable as a result of actions perform by function. 
  • The value of table variable should be derived from a single SELECT statement.
1) Select Function then Right click on Function and select new Inline Table-valued Function in Programmability : 


2) Example - Create Inline Table-valued Function :

3) Inline Table-valued Function Created Successfully :


4) Output :

3) Multi-Statement Table-Valued Function :
  • Multi-statement table-valued function returns a table variable as a result of actions perform by function. 
  • In this a table variable, must be explicitly declared and defined whose value can be derived from a multiple sql statements.
1) Select Function then Right click on Function and select Multi-statement Table-valued Function in Programmability : 
2) Example - Create Multi-statement Table-valued Function :

3) Multi-statement Table-valued Function Created Successfully :


4) Output :




Comments

Post a Comment

Popular posts from this blog

Basic Concept In C Sharp Day 16

Image
Lecture 16 : Basic Concept In C Sharp C Sharp : C#  (pronounced  see sharp , like the musical note C♯, but written with the number sign) is a general-purpose, multi-paradigm programming language encompassing static typing, strong typing, lexically scoped, imperative, declarative, functional, generic, object-oriented (class-based), and component-oriented programming disciplines. C# was developed around 2000 by Microsoft as part of its .NET initiative and later approved as an international standard by Ecma (ECMA-334) in 2002 and ISO (ISO/IEC 23270) in 2003. It was designed by Anders Hejlsberg, and its development team is currently led by Mads Torgersen, being one of the programming languages designed for the Common Language Infrastructure (CLI). The most recent version is 9.0, which was released in 2020 in .NET 5.0 and included in Visual Studio 2019 version 16.8. C-sharp musical note : " C sharp " was inspired by the musical notation whereby a sharp symb
Read more

Understanding C Sharp Day 18

Image
Lecture 18 : Understanding C Sharp   Class Library : A class library  defines types and methods that are called by an application. The  Class Library .DLL  contains program code, data, and resources that can be can used by other programs and are easily implemented into other Visual Studio projects. Create New Class Library Project with Visual Studio :  Create New Project : Select C# in the language tab And then select  Class Library (.NET Framework) and press the NEXT button You can then proceed to give your project a Project Name Call the project  MathLibrary  (in order to copy/paste example code) Press the CREATE button, After you have given a name to your project Building the Class Library .DDL for C# : The following code example is a C# version of the Class Library.  The Class Library .DLL contains program code, data, and resources.  The Class Library in this example will feature a custom Math API that has a set of functions to Substract, Devide and Multiply numbers.
Read more

Concept In Angular Day 14

Image
Lecture 14 : Concept In Angular What is ngModel : ngModel is Angular’s answer to the two-way data binding problem. Previously in AngularJS, there was no way to perform a binding which would update automatically. What are Directives : Directives are markers on a DOM element that tell AngularJS to attach a specified behaviour to that DOM element or even transform the DOM element and its children. Angular includes various built-in directives. In addition to this, you can create custom directives for your application. ng-model Directive : The ng-model directive binds the value of HTML controls (input, select, textarea) to application data. With the ng-model directive you can bind the value of an input field to a variable created in AngularJS. Two-Way Binding : The binding goes both ways. If the user changes the value inside the input field. Two-way binding, we can display a data property as well as an update that property when the user makes changes. Two-way binding
Read more