In SQL, Table data (Table variables) and user-defined functions (downmoon)

Table data and user-defined functions (downmoon) in SQL)
SQL Server 2000 adds Table data: Table data cannot be used to define the column type, can only be used as a T-SQL variable or as a return value for a custom function, the following is an example of a simple table data:

Declare @ TableVar Table
(Cola int Primary Key, Colb char (3 ))
Insert Into @ TableVar Values (1, 'abc ')
Insert Into @ TableVar Values (2, 'def ')
Select * From @ TableVar

The preceding statement defines a table named TableVar with two columns of table variables. Like a common table, table data also has insert
Select. In SQL Server 2000, table data is inseparable from user-defined functions.
2000 two types of functions are supported: built-in functions and user-defined functions. Built-in functions allow T-SQL only
The statement cannot be changed. User-defined functions can be defined as needed. A user-defined function can contain parameters or not, but can only return a single value. For this reason, SQL Server 2000 adds table data, whose values can be integer or numeric. The following example shows the basic structure of a user-defined function:

Create Function CubicVolume
(@ CubeLength decimal (4, 1), @ CubeWidth decimal (4, 1), @ CubeHeight decimal (4, 1 ))
Returns decimal (12, 3)
As
Begin
Return (@ CubeLength * @ CubeWidth * @ CubeHeight)
End
-- SELECT AppDta. dbo. CubicVolume (10, 8, 6)

In
In the above example, create function is used to CREATE a FUNCTION named CubicVolume to calculate the volume of the cube. The variable named CubeLength CubeWidth
CubeHeight is the input parameter, and the return value is Numeric. BEGIN indicates the start of the function body, and END indicates the END of the function body. Example
We will understand how user-defined functions and table-type data are organically combined:

Use pubs

Create Function SalesByStore (@ storeid varchar (30 ))
Returns Table
As
Return (Select title, qty From sales s, titles t
Where s. stor_id = @ storeid and t. title_id = s. title_id)

-- Select * from sales
SELECT Pubs. dbo. SalesByStore (7131)