'Dapper not returning results with additional, option parameter to stored procedure

I am successfully calling a SQL Server stored procedure that has a number of parameters, with the following signature:

Procedure [dbo].[usp_MachineComments] (@Type VARCHAR(250), @UserID VARCHAR(250), @SiteID INT, @MachineCommentID INT = NULL, @MachineComment VARCHAR(2000) = NULL, @ReasonCode INT = NULL, @MachineDate DATETIME = NULL, @ShiftKey INT = NULL)

If I call this proc in SSMS with the following combinations, all is good:

[dbo].[usp_MachineComments] 'View', 'username', 1 or, [dbo].[usp_MachineComments] 'View', 'username', 1, 1

If I call the procedure from code, via Dapper as follows all is OK:

        IEnumerable<AnomalyDto> anomalies;


            var parameters = new DynamicParameters();
            parameters.Add("Type", "View", System.Data.DbType.String);
            parameters.Add("UserID", _authState.User.Identity.Name, System.Data.DbType.String);
            parameters.Add("SiteID", siteId, System.Data.DbType.Int32);

            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                anomalies = await conn.QueryAsync<AnomalyDto>(
                    "usp_MachineComments",
                    parameters,
                    commandType: CommandType.StoredProcedure);

However, the following calls returns no results:

        IEnumerable<AnomalyDto> anomalies;

            var parameters = new DynamicParameters();
            parameters.Add("Type", "View", System.Data.DbType.String);
            parameters.Add("UserID", _authState.User.Identity.Name, System.Data.DbType.String);
            parameters.Add("SiteID", siteId, System.Data.DbType.Int32);
            parameters.Add("ShiftKey", shiftKey, DbType.Int32);

            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                anomalies = conn.Query<AnomalyDto>(
                    "usp_MachineComments",
                    parameters,
                    commandType: CommandType.StoredProcedure);

Any ideas please... a solution eludes me.

dapper


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source

Related Questions