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!!
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; }