Stored procedure and stored function with resultset
This example shows how to use a stored procedure that executes inside itself a parameterized SELECT query to a table and returns table records that satisfy the query condition as the list of corresponding complex type objects.
The script for creating a stored procedure 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 PROCEDURE dbo.SelectDepartmentByLocation(@Loc varchar(13))
WITH
EXECUTE AS CALLER
AS
SELECT * FROM dbo.Department WHERE Loc = @Loc;
GO
First, we create a model and drag the required stored procedure from the Database Explorer window to the design area; in the displayed message box we click Yes to obtain metadata of procedure result set.
As a result we get a complex type and a method corresponding to the stored procedure:
With the following parameters:
And with the following result mapping details:
As a result of code generation for the model, the corresponding method of the model context will be generated having a signature close to the relevant stored procedure:
C#:
public ObjectResult<selectdepartmentbylocationresult> SelectDepartmentByLocation (string Loc)
Visual Basic:
Public Function SelectDepartmentByLocation (ByVal Loc As String) As ObjectResult(Of SelectDepartmentByLocationResult)
Note
In case it is not a stored procedure, but a stored function, drag the function to the Storage part of the Model Explorer window, create the corresponding method, select it in the Methods node and in the Properties window locate the Concealed Function property and set its value to True; the Composable property value is automatically set to False.
If we need to retrieve the whole table, we do the following: first, we create a model and add an entity to it corresponding to the Department table, drag the required stored procedure from the Database Explorer window to the design area; in the displayed message box we click No not to obtain metadata of procedure result set.
Then we double-click the method and in the Value Types dropdown list select the Department table:
The method has the following parameters:
And the following result mapping details:
As a result of code generation for the model, the corresponding method of the model context will be generated having a signature close to the relevant stored procedure:
C#:
public ObjectResult<department> SelectDepartmentByLocation (string Loc)
Visual Basic:
Public Function SelectDepartmentByLocation (ByVal Loc As String) As ObjectResult(Of Department)