Repository navigation
Calling stored procedure with arguments with default values. Wrong types generated in ORM #663
Description
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
Activity
Thorium commentedon Dec 10, 2019
I think the issue is about here:
SQLProvider/src/SQLProvider/SqlDesignTime.fs
Line 374 in 5571082
if this is fixed, I think the Invoke should be overloaded to keep the old Invoke for backward compatibility.
Thorium commentedon Dec 10, 2019
...actually it may have to be read in the provider specific createSprocParameters method already.
dmitryrusak commentedon Dec 17, 2019
When do you think the issue will be fixed? Right now for us it is really a show-stopper..
Thorium commentedon Dec 17, 2019
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 commentedon Mar 30, 2020
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 commentedon Mar 30, 2020
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 commentedon Mar 30, 2020
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 = NULLin the sproc param definition) and supplyNone(null) as the parameter value. You have to supply a properint, which makes it impossible to call when you only have anint option.Thorium commentedon Mar 30, 2020
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 commentedon Mar 31, 2020
Sorry, I was talking about the last part of your comment:
It sounds like you have a solution, but I don't understand what you mean.
Thorium commentedon Apr 20, 2020
Sorry, commented to wrong issue. Deleted the comment.