User-defined functions that return a table data type can be powerful alternatives to views. These functions are referred to as table-valued functions. A table-valued user-defined function can be used where table or view expressions are allowed in SQL queries.
The script for creating a table-valued function and a table for the SQL Server DBMS is as follows:
CREATE TABLE dbo.Department (
DeptNo int NOT NULL,
DName varchar(14),
Loc varchar(13),
PRIMARY KEY (DeptNo)
)
GO
CREATE FUNCTION dbo.Fun_DepartmentByLocation(@Loc varchar(13))
RETURNS TABLE
AS
RETURN (
SELECT * FROM dbo.Department WHERE Loc = @Loc
)
GO
Create a model and add a method to it corresponding to the function (either at the stage of creation at the Select database objects page of Create Model Wizard, or by dragging the corresponding stored function from the Database Explorer window to the diagram area or in the Model Explorer window of an already existing model).
As a result, we will get a method in the model corresponding to the stored function:
With the following parameters:
Click this method in the Model Explorer. In the Properties window, the Table-Valued Function property must be set to True for the method.
As a result of code generation for the model, the corresponding method will be generated having a signature close to the relevant stored function:
C#:
public IList<Department> FunDepartmentByLocation (string Loc)
{
OAParameter LocParameter = new OAParameter();
LocParameter.ParameterName = @"Loc";
LocParameter.Direction = ParameterDirection.Input;
if (Loc != null)
{
LocParameter.Value = Loc;
}
else
{
LocParameter.DbType = DbType.String;
LocParameter.Size = -1;
LocParameter.Value = DBNull.Value;
}
IList<Department> result = this.ExecuteQuery<Department>(@"select * from dbo.Fun_DepartmentByLocation(@Loc)", CommandType.Text, LocParameter);
return result;
}
Now it is possible to use this stored function in the application with the help of method wrapper. This allows working with the stored function with all possible convenience, as wrapper methods are strongly typed, are found by IntelliSense and have signatures close to the corresponding stored functions.
