Stored procedure with default value and ExecuteSqlRaw

Hello,

I have a stored procedure with default value parameters like:

    @Mitglied_ID INT = NULL,
    @Name VARCHAR (255) = NULL,
    @Vorname VARCHAR (255) = NULL,

the problem is that the ExecuteSqlRaw of creates a sql statement which set such parameters if there is a value of null to "DEFAULT" and this results in an error on SQLSERVER

image

Msg 8178, Level 16, State 1, Line 0
The parameterized query "(@returnVal int output, @ Mitglied_ID int, @ Name varchar (14), @ Vorna" expects the @Mitglied_ID parameter, which was not provided.

robert

Please provide the entire stored procedure definition to test it locally. We need just the parameters not the procedure body.

    @Mitglied_ID INT = NULL,
    @Name VARCHAR (255) = NULL,
    @Vorname VARCHAR (255) = NULL,
    @Titel VARCHAR (255) = NULL,
    @Anrede VARCHAR (255) = NULL,
    @Postleitzahl VARCHAR (10) = NULL,
    @Ort VARCHAR (255) = NULL,
    @Strasse VARCHAR (255) = NULL,
    @Geburtsdatum DATETIME2 = NULL,
    @Sozialversicherungsnummer VARCHAR (10) = NULL,

Here is the code generated for this procedure:

public async Task<int> Tests(int? Mitglied_ID, string Name, string Vorname, string Titel, string Anrede, string Postleitzahl, string Ort, string Strasse, DateTime? Geburtsdatum, string Sozialversicherungsnummer)
{
    OnTestsDefaultParams(ref Mitglied_ID, ref Name, ref Vorname, ref Titel, ref Anrede, ref Postleitzahl, ref Ort, ref Strasse, ref Geburtsdatum, ref Sozialversicherungsnummer);

    SqlParameter[] @params =
    {
        new SqlParameter("@returnVal", SqlDbType.Int) {Direction = ParameterDirection.Output},
        new SqlParameter("@Mitglied_ID", SqlDbType.Int) {Direction = ParameterDirection.Input, Value = Mitglied_ID},
        new SqlParameter("@Name", SqlDbType.VarChar) {Direction = ParameterDirection.Input, Value = Name},
        new SqlParameter("@Vorname", SqlDbType.VarChar) {Direction = ParameterDirection.Input, Value = Vorname},
        new SqlParameter("@Titel", SqlDbType.VarChar) {Direction = ParameterDirection.Input, Value = Titel},
        new SqlParameter("@Anrede", SqlDbType.VarChar) {Direction = ParameterDirection.Input, Value = Anrede},
        new SqlParameter("@Postleitzahl", SqlDbType.VarChar) {Direction = ParameterDirection.Input, Value = Postleitzahl},
        new SqlParameter("@Ort", SqlDbType.VarChar) {Direction = ParameterDirection.Input, Value = Ort},
        new SqlParameter("@Strasse", SqlDbType.VarChar) {Direction = ParameterDirection.Input, Value = Strasse},
        new SqlParameter("@Geburtsdatum", SqlDbType.DateTime) {Direction = ParameterDirection.Input, Value =  string.IsNullOrEmpty(Geburtsdatum) ? DBNull.Value : (object)DateTime.Parse(Geburtsdatum, null, System.Globalization.DateTimeStyles.RoundtripKind)},
        new SqlParameter("@Sozialversicherungsnummer", SqlDbType.VarChar) {Direction = ParameterDirection.Input, Value = Sozialversicherungsnummer},
    };
    Context.Database.ExecuteSqlRaw("EXEC @returnVal=[dbo].[Test] @Mitglied_ID, @Name, @Vorname, @Titel, @Anrede, @Postleitzahl, @Ort, @Strasse, @Geburtsdatum, @Sozialversicherungsnummer", @params);

    int result = Convert.ToInt32(@params[0].Value);

    OnTestsInvoke(ref result);

    return await Task.FromResult(result);
}

Can you clarify what's wrong with this code and how should be generated to fit your needs?

it seems this has something to do with Numeric or DropDown control - datepicker values are "null"

it seems it has somethimng to do that all this properties are without $"{}:

    protected async System.Threading.Tasks.Task TemplateForm1Submit(PdbDetektive.Models.Pdb.AssistentPfuscher args)
    { var pdbAssistentPfuschersResult = await Pdb.AssistentPfuschers(args.Mitglied_ID, $"{args.Name}", $"{args.Vorname}", $"{args.Titel}", $"{args.Anrede}", $"{args.Postleitzahl}", $"{args.Ort}", $"{args.Strasse}", $"{args.Geburtsdatum}", $"{args.Sozialversicherungsnummer}", $"{args.Beruf}", args.Firma, $"{args.Notiz}", $"{Security.User.Name}", Akt_ID, Globals.gErmittler_ID, args.Behrde_ID, args.Verfahrensabschluss_ID, $"{args.Ttigkeit}", args.Pfuschertyp, args.Strafe, $"{args.Erhebungsdatum}", $"{args.Anzeigedatum}", $"{args.Einstellungsdatum}", args.bertretung_GEWO, args.bertretung_KBO, $"{args.Notiz}");}

why are some of them with $"{} and some without?

found that the problem are type like int? and bool?

I found tow solutions for nullable int and bool:

          new SqlParameter("@Mitglied_ID", SqlDbType.Int) {Direction = ParameterDirection.Input, Value = Mitglied_ID ?? (object)DBNull.Value},

or

          new SqlParameter("@Mitglied_ID", SqlDbType.Int) {Direction = ParameterDirection.Input, Value = Mitglied_ID.HasValue ? Mitglied_ID : (object)DBNull.Value},

robert

please add one of the soutions above to Radzen so correct code is generated...

We will handle this in our update later today.

UPDATE: Unfortunately we cannot pass DBNull.Value when the parameter is null since in this case we will mask the EF exception. For example Northwind GetProductDetails(int? ProductId):

      public async Task<Models.Northwind.GetProductDetailResult> GetProductDetails(int? ProductId)
      {
          OnGetProductDetailsDefaultParams(ref ProductId);

          SqlParameter[] @params =
          {
              new SqlParameter("@returnVal", SqlDbType.Int) {Direction = ParameterDirection.Output},
              new SqlParameter("@ProductId", SqlDbType.Int) {Direction = ParameterDirection.Input, Value = ProductId},
              new SqlParameter("@ProductName", SqlDbType.VarChar, 100) {Direction = ParameterDirection.Output},
              new SqlParameter("@UnitPrice", SqlDbType.Decimal) {Direction = ParameterDirection.Output},
              new SqlParameter("@QuantityPerUnit", SqlDbType.VarChar, 20) {Direction = ParameterDirection.Output},
          };

          Context.Database.ExecuteSqlRaw("EXEC @returnVal=[dbo].[GetProductDetails] @ProductId, @ProductName out, @UnitPrice out, @QuantityPerUnit out", @params);

          var result = new Models.Northwind.GetProductDetailResult();
          result.returnValue = Convert.ToInt32(@params[0].Value);
          result.ProductName = Convert.ToString(@params[2].Value);
          result.UnitPrice = Convert.ToDecimal(@params[3].Value);
          result.QuantityPerUnit = Convert.ToString(@params[4].Value);

          OnGetProductDetailsInvoke(ref result);

          return await Task.FromResult(result);
      }

If we pass null to this method we will receive the following exception:

Error: Microsoft.Data.SqlClient.SqlException (0x80131904): The parameterized query '(@returnVal int output,@ProductId int,@ProductName varchar(100) ' expects the parameter '@ProductId', which was not supplied.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String methodName)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, IEnumerable`1 parameters)
   at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.ExecuteSqlRaw(DatabaseFacade databaseFacade, String sql, Object[] parameters)
   at NorthwindBlazor.NorthwindService.GetProductDetails(Nullable`1 ProductId) in C:\Projects\NorthwindBlazor\server\Services\NorthwindService.cs:line 1173
   at NorthwindBlazor.Pages.MainPageComponent.Load() in C:\Projects\NorthwindBlazor\server\Pages\MainPage.razor.designer.cs:line 75
   at NorthwindBlazor.Pages.MainPageComponent.OnInitializedAsync() in C:\Projects\NorthwindBlazor\server\Pages\MainPage.razor.designer.cs:line 71
   at Microsoft.AspNetCore.Components.ComponentBase.RunInitAndSetParametersAsync()
ClientConnectionId:32591c1c-7fc6-4ce2-89a3-d2ec41258362
Error Number:8178,State:1,Class:16

If we pass DBNull.Value the procedure will be executed and later an exception will be raised when attempting to read the output parameters since they will be DBNull.Value which is not correct in my opinion. If a parameter is missing or null it will be better to receive an exception.

Hi, I don't really understand what you mean - if the paramater in the Stored Procedure is nullable why not sending "NULL"?

can you send me the script for the GetProductDetails Stored Procedure?

it seems that EF Core produses the wrong? SQL - instead of sending null it creates "DEFAULT" which is not allowed in SQLServer in this case - in my sample I have an INSERT SP not an SELECT...

I have now created the same stored procedure with EF Core Power Tools ( ErikEJ/EFCorePowerTools: Entity Framework Core Power Tools - reverse engineering, migrations and model visualization for EF Core (github.com))

the code is what I have been expected - it generates:
Value = Mitglied_ID ?? Convert.DBNull

        public virtual async Task<int> ASSISTENT_PfuscherAsync(int? Mitglied_ID, string Name, string Vorname, string Titel, string Anrede, string Postleitzahl, string Ort, string Strasse, DateTime? Geburtsdatum, string Sozialversicherungsnummer, string Beruf, bool? Firma, string Notiz, string Benutzer, int? Akt_ID, int? Ermittler_ID, int? Behörde_ID, int? Verfahrensabschluss_ID, string Tätigkeit, int? Pfuschertyp, decimal? Strafe, DateTime? Erhebungsdatum, DateTime? Anzeigedatum, DateTime? Einstellungsdatum, bool? Übertretung_GEWO, bool? Übertretung_KBO, string PfuscherbeziehungAktNotiz, OutputParameter<int> returnValue = null, CancellationToken cancellationToken = default)
        {
            var parameterreturnValue = new SqlParameter
            {
                ParameterName = "returnValue",
                Direction = System.Data.ParameterDirection.Output,
                SqlDbType = System.Data.SqlDbType.Int,
            };

            var sqlParameters = new []
            {
                new SqlParameter
                {
                    ParameterName = "Mitglied_ID",
                    Value = Mitglied_ID ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Int,
                },
                new SqlParameter
                {
                    ParameterName = "Name",
                    Size = 255,
                    Value = Name ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Vorname",
                    Size = 255,
                    Value = Vorname ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Titel",
                    Size = 255,
                    Value = Titel ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Anrede",
                    Size = 255,
                    Value = Anrede ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Postleitzahl",
                    Size = 10,
                    Value = Postleitzahl ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Ort",
                    Size = 255,
                    Value = Ort ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Strasse",
                    Size = 255,
                    Value = Strasse ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Geburtsdatum",
                    Scale = 7,
                    Value = Geburtsdatum ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.DateTime2,
                },
                new SqlParameter
                {
                    ParameterName = "Sozialversicherungsnummer",
                    Size = 10,
                    Value = Sozialversicherungsnummer ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Beruf",
                    Size = 255,
                    Value = Beruf ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Firma",
                    Value = Firma ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Bit,
                },
                new SqlParameter
                {
                    ParameterName = "Notiz",
                    Size = 8000,
                    Value = Notiz ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Benutzer",
                    Size = 100,
                    Value = Benutzer ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Akt_ID",
                    Value = Akt_ID ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Int,
                },
                new SqlParameter
                {
                    ParameterName = "Ermittler_ID",
                    Value = Ermittler_ID ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Int,
                },
                new SqlParameter
                {
                    ParameterName = "Behörde_ID",
                    Value = Behörde_ID ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Int,
                },
                new SqlParameter
                {
                    ParameterName = "Verfahrensabschluss_ID",
                    Value = Verfahrensabschluss_ID ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Int,
                },
                new SqlParameter
                {
                    ParameterName = "Tätigkeit",
                    Size = 255,
                    Value = Tätigkeit ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                new SqlParameter
                {
                    ParameterName = "Pfuschertyp",
                    Value = Pfuschertyp ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Int,
                },
                new SqlParameter
                {
                    ParameterName = "Strafe",
                    Precision = 19,
                    Scale = 4,
                    Value = Strafe ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Money,
                },
                new SqlParameter
                {
                    ParameterName = "Erhebungsdatum",
                    Scale = 7,
                    Value = Erhebungsdatum ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.DateTime2,
                },
                new SqlParameter
                {
                    ParameterName = "Anzeigedatum",
                    Scale = 7,
                    Value = Anzeigedatum ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.DateTime2,
                },
                new SqlParameter
                {
                    ParameterName = "Einstellungsdatum",
                    Scale = 7,
                    Value = Einstellungsdatum ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.DateTime2,
                },
                new SqlParameter
                {
                    ParameterName = "√úbertretung_GEWO",
                    Value = √úbertretung_GEWO ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Bit,
                },
                new SqlParameter
                {
                    ParameterName = "√úbertretung_KBO",
                    Value = √úbertretung_KBO ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.Bit,
                },
                new SqlParameter
                {
                    ParameterName = "PfuscherbeziehungAktNotiz",
                    Size = 8000,
                    Value = PfuscherbeziehungAktNotiz ?? Convert.DBNull,
                    SqlDbType = System.Data.SqlDbType.VarChar,
                },
                parameterreturnValue,
            };
            var _ = await _context.Database.ExecuteSqlRawAsync("EXEC @returnValue = [dbo].[ASSISTENT_Pfuscher] @Mitglied_ID, @Name, @Vorname, @Titel, @Anrede, @Postleitzahl, @Ort, @Strasse, @Geburtsdatum, @Sozialversicherungsnummer, @Beruf, @Firma, @Notiz, @Benutzer, @Akt_ID, @Ermittler_ID, @Behörde_ID, @Verfahrensabschluss_ID, @Tätigkeit, @Pfuschertyp, @Strafe, @Erhebungsdatum, @Anzeigedatum, @Einstellungsdatum, @Übertretung_GEWO, @Übertretung_KBO, @PfuscherbeziehungAktNotiz", sqlParameters, cancellationToken);

            returnValue?.SetValue(parameterreturnValue.Value);

            return _;
        }

robert

Hi,

We've decided after all to change that. You can get the latest build of Radzen for Windows from here.

1 Like

Thanks a lot - very good solution:

      foreach(var p in @params)
      {
          if(p.Direction == ParameterDirection.Input && p.Value == null)
          {
              p.Value = DBNull.Value;
          }
      }