Azure SQL Database: "No columns were selected" when calling a stored procedure using Dapper

azure azure-sql-database dapper stored-procedures

Question

I have a really simple stored procedure in Azure SQL Database:

CREATE PROCEDURE dbo.spfindPartialIdentity 
    @ClientId nvarchar(50),
    @ExternalId nvarchar(250)
AS BEGIN
   SET NOCOUNT ON;

   SELECT 
       ClientId, ExternalId 
   FROM 
       [dbo].[PartialIdentities] 
   WHERE 
       ClientId = @ClientId AND ExternalId = @ExternalId
END

and I invoke it using Dapper from my repository:

using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ReportingDB"].ConnectionString))
{
    await conn.OpenAsync();
    var res = await conn.QueryAsync<PartialIdentity>("dbo.spfindPartialIdentity", 
                                                      new { ClientId = clientId, ExternalId = externalId }, 
                                                      commandType: CommandType.StoredProcedure, 
                                                      commandTimeout: GetCommandTimeout());

    return res.ToList();
}

Really straightforward. I works fine, BUT when the stored procedure returns no results I got a

'System.InvalidOperationException' in Dapper.dll, "No columns were selected"

exception when calling QueryAsync(). My DTO is really simple as well:

public class PartialIdentity
{
    public PartialIdentity(string clientId, string externalId)
    {
        this.ClientId = clientId;
        this.ExternalId = externalId;
    }

    public string ClientId { get; set; }
    public string ExternalId { get; set; }
}

I've tried QueryMultipleAsync() as well, same result.

What is the problem? Thanks in advance

Accepted Answer

It turned out to be bug in my SP, the Dapper code was just fine. The SP was actually a bit more complex (with a some nested IF-THEN-ELSE), and when no records matched the condition it did not do anything, did not return anything and the poor Dapper did not know how to map that to the DTO - hence the exception. Duh! Silly me! Documenting that in case someone else have a similar case.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why