Share
Explore BrainMass

simple SQL program demonstrating the use of CREATE FUNCTION command in SQL

I need a simple SQL program demonstrating the use of CREATE FUNCTION command in SQL. A create table can be used to generate a simple record of items such as name, title, age. The Create Function command will be used to retrieve one specific value.

The program should demonstrate and show how you can pass a variable in and output a variable. Anything that would demonstrate this concept would be great. I am open to any ideas.

Solution Preview

The CREATE FUNCTION (SQL Scalar, Table, or Row) statement is used to define a user-defined SQL scalar, table, or row function. A scalar function returns a single value each time it is invoked, and is generally valid wherever an SQL expression is valid. A table function can be used in a FROM clause and returns a table. A row function can be used as a transform function and returns a row.
Valid only if RETURNS specifies a table (TABLE column-list)
Valid only if RETURNS specifies a scalar result (data-type2)

Example 1: (which return table)
CREATE FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
RETURNS TABLE (EMPNO CHAR(6),
LASTNAME ...

Solution Summary

This job models a simple SQL program demonstrating the use of CREATE FUNCTION command in SQL.

$2.19