This example shows how to use a stored procedure with output parameters.
The script for creating a stored procedure 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.DeptsByLocation
(@location varchar(13), @count int OUTPUT)
AS
select * from dbo.Department where loc = @location;
SET @count = (select count(*) from dbo.Department where loc = @location)
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 List<deptoutparamresult> DeptsByLocation (string location, ref global::System.Nullable<int> count)
Visual Basic:
Public Function DeptsByLocation (ByVal location As String, ByRef count As Global.System.Nullable(Of Integer)) As List(Of DeptOutParamResult)
If we need to retrieve the whole table, we do the following: 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 drop-down 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 List<department> DeptsByLocation (string location, ref global::System.Nullable<int> count)
Visual Basic:
Public Function DeptsByLocation (ByVal location As String, ByRef count As Global.System.Nullable(Of Integer)) As List(Of Department)
