Calling stored procedure with arguments with default values. Wrong types generated in ORM

[dmitryrusak]

Description

If the SQL procedure has the input argument with default NULL value like f.e.

create procedure [dbo].[create_config]
   @CreateDate datetime = NULL,
   @UpdateDate datetime = NULL,
   @DeleteDate datetime = NULL,
   @Parm1 int = NULL
   ...
as
   …

then the SQLProvider creates the function for calling this procedure, but the type of the CreateDate is DateTime (ie not the Option or Nullable). I have a lot of stored procedures with lots of default arguments. How to get the correct parameter type in the fsharp function?

Related information

• Used database MS SQL server
• Operating system WIndows 10
• Branch
• .NET FW 4.7.2

[Thorium]

I think the issue is about here:

https://github.com/fsprojects/SQLProvider/blob/5571082aac21f7a720f47dea273d9f7e2c4d7115/src/SQLProvider/SqlDesignTime.fs#L374

if this is fixed, I think the Invoke should be overloaded to keep the old Invoke for backward compatibility.

[Thorium]

...actually it may have to be read in the provider specific createSprocParameters method already.

[dmitryrusak]

When do you think the issue will be fixed? Right now for us it is really a show-stopper..

[Thorium]

I was thinking you are creating a PR so wasn't looking for this for more. :-)

Now that I have, I have some bad news. It seems that you cannot get the indication if a column has a default value or not from the SQL-server schema. https://stackoverflow.com/a/47485321/17791 It also seems that all the input parameters render as nullables, so using that is not heping.

[cmeeren]

I'm hitting this, too.

SqlClient manages this fine, so it seems it's possible.

In order for SqlProvider to be usable with sprocs, a workaround might be to have an option that forces all sproc parameters to be optional. We lose some type safety, but at least sprocs are usable at all. :)

[Thorium]

I was thinking that could we give another overload for the procedure invoke that would take e.g. anonymous record that would map the parameters.

(edit: e.g. taking an object, and reading it by reflection, a bit slow, but I guess usability over performance in this special case would be ok.)

...but it seemed a bit complex, so in my use case I just called the procedures with creating the values in my business logic (e.g. will you create an uniqueidentifier in SQL or Guid in .NET doesn't actually matter so much).

[cmeeren]

Sorry, you lost me. My problem is that there is no way to call an sproc that takes e.g. a nullable INT (e.g. @paramName INT = NULL in the sproc param definition) and supply None (null) as the parameter value. You have to supply a proper int, which makes it impossible to call when you only have an int option.

[Thorium]

I was thinking that besides the current .Invoke(1, "ABC", "foo", 42) there would be also .Invoke {| Diameter = 42; Area = "foo" |} ...and it'd be totally up to user to provide the required parameters, no compiler help here, if you don't provide something, it'd be default value. But as I said, that was just an idea of concept, I haven't actually looked into this.

[cmeeren]

Sorry, I was talking about the last part of your comment:

...but it seemed a bit complex, so in my use case I just called the procedures with creating the values in my business logic (e.g. will you create an uniqueidentifier in SQL or Guid in .NET doesn't actually matter so much).

It sounds like you have a solution, but I don't understand what you mean.

[Thorium]

Sorry, commented to wrong issue. Deleted the comment.