Stored procedure is not returning rows with dynamic query, Dapper

Question

Stored procedure is not returning rows with dynamic query, Dapper

c# dapper sql-server stored-procedures

Question

I've created a stored procedure with dynamic query. The values are returned to my app in C# with Dapper.

The following code works, the rows are returned to my app and are shown in SQL Server.

set @query = 'Select * from invoices';
exec sp_executesql @query

But when I build the query, something like this:

Dynamic query build:

'SELECT * FROM Invoices AS i    
 LEFT JOIN InvoiceInformations AS ii ON i.InvoiceId = ii.InvoiceId  
 WHERE 1 = 1';

exec sp_executesql @query

The rows are displayed correctly in SQL Server, but in C#, I got nothing.

It's important to say that it only happens when I build the query dynamically, but the query is ok, I printed it, run it manually and it works.

C# code (this works):

var invoices = await dbConnection.QueryAsync<Invoices>("sp_get_invoices", commandType: CommandType.StoredProcedure);

Please help!!

Magux

View more on Stack Overflow

Magux · Accepted Answer · 7/16/2018 4:46:00 PM

I've resolved it, I was looking in the stored procedure but it was ok. the problem was in the parameters of Dapper.

I had some values NOT NULLABLES, like InvoiceId

var invoices = await dbConnection.QueryAsync<Invoices>("sp_get_invoices",
                    new 
                    {
                        InvoiceId = filter.InvoiceId,
                        Currency = filter.Currency
                    }, commandType: CommandType.StoredProcedure);

my stored procedure verifies:

if (InvoiceId IS NOT NULL)
  SET @Query = @Query + ' AND i.InvoiceId = ' + CAST(@InvoiceId as varchar(10));

I just nullable it on C#

        public int? InvoiceId { get; set; }

Stored procedure is not returning rows with dynamic query, Dapper

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator