Menu Bar

Home
Introduction to HTML Tables Elements and Attributes Lists Google Home Page Registration Form Forms_1 Forms_2
Introduction to CSS Three types of css Tables Forms(id,class,psuedo class) Backgrounds Periodic table Border in a table Margins and Padding Height,Width,Overflow
Introduction Basic Script Data types and Operators String methods and Arrays Functions and Objects Events Dialog boxes Conditions Loops Error Handling External JS Simple Calculator
Introduction SQL Installation and User creation SQL:Database and Table Creation SQL:Alter Constraints Select,Insert Delete Operators Functions Joins, Union, GroupBy Missing Items User Defined Functions Views Backup and Restore Company DB, Table and records Queries on Company DB Computer Business DB and Queries
Introduction Variables, Datatypes & Operators Conditional & Looping Statements Functions Pointers & Recursion Array & Files Patterns
Introduction Problems in C++
Configuration SQL server connection Introduction to PHP
Introduction VB.NET VB.NET Compilation & Run VB.NET Problems No link
Design Principles Stone Profit support page MasterLegacy MasterDev Work with Live System

User Defined Functions

User Defined Functions:

User Defined Functions is a programming construct that accepts parameters, does actions and returns the result of that action. The result either is a scalar value or result set. User Defined Functions support modular programming. Once a User Defined Function is create and store in a database then it can be called any number of times. They can reduce network traffic and reduce the compilation cost.
Types of User Defined Functions:

  1. Scalar Functions
  2. Table Valued Functions

1)Scalar Functions: A Scalar Function accepts zero or more parameters and return a single value. The return type of a scalar function is any data type except text, ntext, image, cursor and timestamp. Scalar functions can be use in a WHERE clause of the SQL Query.










2)Table Valued Functions: A Table Valued Function accepts zero or more parameters and return a table variable. This type of function is special because it returns a table that you can query the results of a join with other tables. A Table Valued function is further categorized into an “Inline Table Valued Function” and a “Multi-Statement Table Valued Function”.
a)Inline Table Valued Function: An Inline Table Valued Function contains a single statement that must be a SELECT statement. The result of the query becomes the return value of the function. There is no need for a BEGIN-END block in an Inline function.






b)Multi-Statement Table Valued Function: A Multi-Statement contains multiple SQL statements enclosed in BEGIN-END blocks. In the function body you can read data from databases and do some operations. In a Multi-Statement Table valued function the return value is declared as a table variable and includes the full structure of the table to be returned. The RETURN statement is without a value and the declared table variable is returned.






Scalar Function: Write a function to get the customer sales as of a particular date

  • Function (CustomerID, OrderDate)
  • Return total sale for that day






Table-Valued Functions: Write a function to print multiplication table

  • Function(number(8) int)
  • Return table like:
8 X 1 = 8
8 X 2 = 16
.
.
.
8 X 10 = 80




Subscribe to: Posts (Atom)