Solved it.

Instead of the stored procedure I created a function:

CREATE FUNCTION dbo.dnn_f_CountOutdatedRecords
(
   @ModuleID int,
   @StartDate datetime
)
RETURNS int
AS
BEGIN
   DECLARE @OutdatedRecordsCount int

   SELECT
      @OutdatedRecordsCount = COUNT(*)
   FROM
      dbo.dnn_Records
   WHERE
      ModuleID = @ModuleID
      AND StartDate <= @StartDate

   RETURN @OutdatedRecordsCount
END
GO

In the controller I use the follwing code:

public static int OutdatedRecordsCount(int moduleID, DateTime startDate)
{
   int outdatedRecordsCount = 0;
   using (IDataContext ctx = DataContext.Instance())
   {
      outdatedRecordsCount = ctx.ExecuteScalar(System.Data.CommandType.Text, "SELECT {databaseOwner}{objectQualifier}f_CountOutdatedRecords(@0, @1)", new object[] { moduleID, startDate });
   }
   return outdatedRecordsCount;
}

Eh, voila!

Happy DNNing!
Michael

Anyway, if someone could post an example (or write a blog) of how to work with stored procedures that return an output parameter in DAL2 - this would be interesting.

Happy DNNing!
Michael

If I wanted to get a single value from a stored procedure, this is how I'd do it:

CREATE OR ALTER PROCEDURE CountOutdatedRecords (
    @ModuleId int,
    @StartDate datetime
)
AS
BEGIN
    SELECT @moduleId + DATEDIFF(ns, GETUTCDATE(), @StartDate)
END
GO

From the SQL, just SELECT the value you want.

protected int GetValue()
{
    using (IDataContext ctx = DataContext.Instance())
    {
        return ctx.ExecuteScalar<int>(
            System.Data.CommandType.StoredProcedure, 
            "{databaseOwner}{objectQualifier}CountOutdatedRecords", 
            new object[] { ModuleConfiguration.ModuleID, DateTime.Now });
    }
}

Using ExecuteScalar will get that single value from the SELECT result set.

If you need to use an OUTPUT parameter, this will work:

CREATE OR ALTER PROCEDURE CountOutdatedRecords (
  @ModuleId int,
  @StartDate datetime,
  @OutdatedRecordsCount int OUTPUT
)
AS
BEGIN
    SELECT @moduleId + DATEDIFF(ns, GETUTCDATE(), @StartDate);
    SET @OutdatedRecordsCount = 123;
END
GO

protected int GetOutputValue()
{
    using (IDataContext ctx = DataContext.Instance())
    {
        var count = new SqlParameter("@OutdatedRecordsCount", SqlDbType.Int) 
            { 
                Direction = ParameterDirection.Output 
            };
        ctx.Execute(
            CommandType.StoredProcedure, 
            "{databaseOwner}{objectQualifier}CountOutdatedRecords", 
            new SqlParameter("@ModuleId", ModuleConfiguration.ModuleID), 
            new SqlParameter("@StartDate", DateTime.Now),
            count);

        return (int)theResult.Value;
    }
}

Hope it helps!

Thanks Brian, I'll try that.

Happy DNNing!
Michael