Table-value Functions

In a form, rather than use of multi-statement SQL scripts or stored procedures with parameters, complex datasets are supported using SQL table-valued functions.

Much like the configuration of a traditional query on a table or a view. the columns included in the dataset are based upon the columns selected by the user.

The Parameters (if any) of the SQL function do not need to be considered when defining the dataset. When the dataset is used in the Form Designer, the properties of the controls in the form can be mapped to the parameters of the SQL function (if any).

An example table-valued function is shown below:

CREATE FUNCTION dbo.[ufn_GB_Usr_GetDrillholeLength]
  (@Project NVarchar(16),
   @SiteId  NVarchar(16))
RETURNS @ResultSet TABLE
  (PROJECT     NVarchar(16),
   SITE_ID     NVarchar(16),
   HOLE_LENGHT    Float,
   START_DEPTH    Float,
   END_DEPTH      Float)

AS 
BEGIN
	INSERT @ResultSet
	SELECT
	PROJECT,
       SITE_ID,
       END_DEPTH - ISNULL(START_DEPTH, 0) AS HOLE_LENGTH,
       START_DEPTH,
       END_DEPTH
	FROM
		dbo.GB_SITE
	WHERE
		PROJECT = @Project
       AND SITE_ID = @SiteId
RETURN

END

Data