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:

images_SP-with-Resultset-General-TDA

With the following parameters:

images_SP-with-Resultset-params-TDA

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 IList<FunDepartmentByLocationResult> SelectDepartmentByLocation (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<FunDepartmentByLocationResult> result = this.ExecuteQuery<FunDepartmentByLocationResult>(@"dbo.SelectDepartmentByLocation", CommandType.StoredProcedure, LocParameter);
            return result;
        }

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 drop-down list select the Department table:

images_SP-with-Resultset-General2-TDA

The method has the following parameters:

images_SP-with-Resultset-params-TDA

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 IList<Department> SelectDepartmentByLocation (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>(@"dbo.SelectDepartmentByLocation", CommandType.StoredProcedure, LocParameter);
            return result;
        }

Stored Procedure and Stored Function With Resultset

See Also