How to call SQL Server table-valued function?

ServiceStack.OrmLite SQL Server
1

I have a table-valued function in SQL Server and I am struggling to find a syntax for executing the function from my ServiceInterface class.

Is there any example of calling a table-valued function with multiple in parameters?

In SQL, the function can be executed:

--SQL function syntax:

DECLARE @BankAccountId UNIQUEIDENTIFIER
DECLARE @StartDate date
DECLARE @EndDate date

SET @BankAccountId = '91a98242-9ff0-ed21-9126-00165d017f21'
SET @StartDate = '2022-07-06'
SET @EndDate = '2022-10-01' 

SELECT
    * 
FROM Bank.Ufn_AccountData(@BankAccountId, @StartDate, @EndDate)

I can workaround the problem by wrapping the SQL function inside an SQL stored procedure and executing it using the syntax:

private List<AccountEntry> GetAccountEntries(Guid accountId, DateOnly startDate, DateOnly endDate)
{
    using var db = AutoQuery!.GetDb<AccountEntry>(Request);

    using var cmd = db.SqlProc("Bank.Usp_Get_AccountData");
    cmd.AddParam("BankAccountId", accountId);
    cmd.AddParam("StartDate", IsoDateTimeHelper.ToDateTime(startDate));
    cmd.AddParam("EndDate", IsoDateTimeHelper.ToDateTime(endDate));
    var results = cmd.ConvertToList<AccountEntry>();
    return results;
}

FYI: ServiceStack 6.5.1; .NET7; SQL Server 2019

2

It’s not really a workaround, this is the most flexible way to do it.

You can use use SqlList for simple SPs:

List<Poco> results = db.SqlList<Poco>("EXEC GetAnalyticsForWeek 1");
List<Poco> results = db.SqlList<Poco>(
    "EXEC GetAnalyticsForWeek @weekNo", new { weekNo = 1 });

List<int> results = db.SqlList<int>("EXEC GetTotalsForWeek 1");
List<int> results = db.SqlList<int>(
    "EXEC GetTotalsForWeek @weekNo", new { weekNo = 1 });

int result = db.SqlScalar<int>("SELECT 10");

There’s also support for using an anonymous type like:

using var cmd = db.SqlProc(spName, new { A = 1 });
var results = cmd.ConvertToList<Table>();

But you have less control over each parameter, e.g. you’d need to use AddParam() to call Stored Procedures with output params.

More examples in:

3

I had tried using SqlList first, but I was getting a runtime error, but I failed to interpret what it was telling me.

On retrying today, I noticed a missing bracket in the query string. With the corrected query string the following SqlList works:

private List<AccountEntry> GetAccountEntries(Guid accountId, DateOnly startDate, DateOnly endDate)
{
    using var db = AutoQuery!.GetDb<CashSheet>(Request);

    List<AccountEntry> results = db.SqlList<AccountEntry>(
        "SELECT * FROM Bank.Ufn_AccountData(@BankAccountId, @StartDate, @EndDate)",
        new
        {
            BankAccountId = accountId,
            StartDate = IsoDateTimeHelper.ToDateTime(startDate),
            EndDate = IsoDateTimeHelper.ToDateTime(endDate)
        });

    return results;
}

Problem solved. Thank you.

1 Like