How to execute stored procedure with OUTPUT parameter?

[StefanBelo]

I have got MSSQL Express stored procedure with OUTPUT parameter, but the invoke method maps this parameter like entry one, not with reference.

Does SQLProvider support such operation?

[pezipink]

Hello, yes we do support OUT parameters in a round-about manner. I believe they are returned as part of the result set(s) of the sproc rather than attempting to use byref params which are kind of nasty in F#.

//cc @colinbull

edit: I don;t think you should see it as a input parameter unless it is marked as INOUT rather than just OUT

[StefanBelo]

Thanks, but it seems I am missing something. When executing stored procedure by calling Invoke, the Invoke method returned value is unit. So what you suggest is that I must change stored procedure to return one record containing my OUTPUT value?

[pezipink]

No, it should work for you. It may be you have ran into an edge-case, I will try and have a look at it a bit later for you unless someone else gets there first. Is it possible to share the definition of the procedure? or the salient bits of it at least.

[StefanBelo]

Here is my database schema, including all tables, views and stored procedures:

http://bfexplorer.net/Content/Downloads/Scripts/MyRaceDatabaseScript.zip

[StefanBelo]

Any news? I tried SqlProgrammabilityProvider and it detects OUTPUT parameters for all my stored procedures.

[pezipink]

I have examined this and it looks like we are simply not supporting output parameters properly. This will need a bit of work since the sproc code is fairly complex - I'll see if I can get a prototype going later in the day, but I might not be able to.

@colinbull I am not missing something silly am I?

[StefanBelo]

Thank you, I will check your progress later, for now will use SqlProgrammabilityProvider.

[colinbull]

@pezipink I know other providers do support out parameters, but looking at https://github.com/fsprojects/SQLProvider/blob/master/src/SQLProvider/Providers.MsSqlServer.fs#L240, it would appears that we explicitly exclude them. I cannot remember for the life of me why this is, but I remember having (what seemed like) a good reason for it at the time.