How to work with User Defined Functions in MSSQL Server?
In this step by step practical video, we learn:
1. What is a Function?
2. What are the types of User Defined Functions?
3. What are the types of System Predefined Functions?
4. How to use Functions?
5. When to use Views? When to use Functions? When to use Procedures?
6. More.. !
FUNCTIONS: DATABASE OBJECTS USED TO STORE SELECT & DML QUERIES
PURPOSE : FUNCTIONS ARE USED FOR CALCULATIONS, DATA ANALYSIS & MONITORING.
EX: REPORT LIST OF EMPLOYEES FROM A GIVEN COUNTRY
EX: REPORT NUMBER OF EMPLOYEES FROM A GIVEN COUNTRY
TYPES OF FUNCTIONS:
1.USER DEFINED FUNCTIONS
1a. SCALAR VALUE RETURNING FUNCTIONS: THESE FUNCTIONS RETURN SINGLE VALUE. EX: SUM OF THREE NUMBERS
SYNTAX FOR SCALAR VALUE RETURNING FUNCTION
CREATE FUNCTION <FUNCTION NAME> (@PARAMETERNAME DATATYPE)
RETURNS <<DATA TYPE>> — ANY DATA TYPE OTHER THAN TABLE
AS
BEGIN
STATEMENT 1
STATEMENT 2
STATEMENT 3…
RETURN
(
<< SELECT QUERY OR VALUE >>
)
END
1b. INLINE TABLE VALUE RETURNING FUNCTIONS
THESE FUNCTIONS RETURN SINGLE TABLE ( ARRAY OR COLLECITON OF VALUES)
THESE FUNCTIONS DO NOT CONTAIN ANY STATEMENT EXCEPT RETURN.
EX: REPORT LIST OF EMPLOYEES FROM A GIVEN COUNTRY
SYNTAX FOR INLINE TABLE VALUE RETURNING FUNCTION
CREATE FUNCTION <FUNCTION NAME>(@PARAMETERNAME DATATYPE)
RETURNS table –table is a data type
AS
RETURN
(
<< SELECT QUERY OR VALUE >>
)
1c. MULTI LINE TABLE VALUE RETURNING FUNCTIONS
THESE FUNCTIONS SHOULD CONTAIN ADDITIONAL STATEMENTS OTHER THAN RETURN STATEMENT.
THESE FUNCTIONS MUST BE DEFINED WITH BEGIN…END STATEMENTS.
EX: REPORT LIST OF ALPHANUMERIC VALUES
SYNTAX FOR MULTI LINE TABLE VALUE RETURNING FUNCTION
CREATE FUNCTION <FUNCTION NAME>(@PARAMETERNAME DATATYPE)
RETURNS table –table is a data type
AS
BEGIN
STATEMENT 1
STATEMENT 2 …
RETURN
END
Comparing the Syntax for User Defined Functions:
2. SYSTEM PREDEFINED FUNCTIONS
2a. SCALAR VALUE RETURNING FUNCTIONS
THESE FUNCTIONS RETURN SINGLE VALUE.
EX: REPORT CURRENT DATE
2b.INLINE TABLE VALUE RETURNING FUNCTIONS
THESE FUNCTIONS RETURN SINGLE TABLE (ARRAY: A COLLECTION OF ITEMS)
EX: REPORT FRAGMENTATION DETAILS OF ALL INDEXES IN A DATABASE
CONCLUSION:
WHEN TO USE FUNCTIONS?
IF WE NEED TO PERFORM ADDITIONAL OPERATIONS ON THE RESULT, USE FUNCTION.
Example: SELECT * FROM fnReportObjectsList(‘U’) WHERE NAME LIKE ‘S%’
WHEN TO USE PROCEDURES?
IF WE NEED TO PERFORM SPEFIC OPERATION WITHIN THE OBJECT (SP) ITSELF.
Example: EXECUTE spReportObjectList(‘U)
| Database Objects | Purpose | Realtime Usage |
| VIEWS
|
Used to store SELECT Query | Used in Reporting |
| FUNCTIONS
|
Used to store SELECT, DML Queries
Support Parameters |
Used for dynamic Reporting
Used for calculations |
| PROCEDURES | Used to store SELECT, DML, DDL, DCL, TCL Queries
Support Parameters |
Used for dynamic Reporting
Used for programming |
At SQL School, we assure you:
✅ Step by step Practical Classes
✅ 100% Hands-On
✅ Highly Interactive
✅ Latest, useful FAQs (Concept Wise)
✅ Certification & One-One Resume Guidance
Why SQL School?
👉🏻 ISO Certified, MSME Registered (Govt. Of India), 40+ MNC Clients
👉🏻 Our Training Content & Certificate adds more value to your resume !
👉🏻 Latest Schedules, Free Demo: www.sqlschool.com/Register
Course inquiries: pls reach us: +91966440801 (Whatsapp, Call)
contact@sqlschool.com
www.sqlschool.com
Lets stay connected and Join our WhatsApp Channel “SQL School” for free technical webinars, Interview FAQs and more : https://whatsapp.com/channel/0029VamSJLI35fLmVEvCPD3r
To join our MSSQL, TSQL Training course, pls visit:
Our Youtube Channel: https://youtube.com/sequelschool
Thank You!
Meet you in our next blog !
