IndexOutOfRangeException when invoking a stored procedure

Describe the bug
When using InvokeAsync on a stored procedure while using SqlProvider.MsSql the list of input parameters include all of the input parameters defined by the stored procedure given a direction of Input as well as the result set given a direction of Output. The list of values provided match the number of input parameters marked with a direction of Input. When the input parameters are processed in order to map them to values and create command parameters, the input parameters are filtered to remove those with a direction of ReturnValue, then attempt to map over the values. In my specific situation where I have a stored procedure with five parameters and one result set, I have six input parameters and five values. After filtering out direction of ReturnValue there are still six input parameters, and after processing the first five input parameters commands are created mapped to the five values provided, but then the sixth input parameter which has a direction of Output results in an IndexOutOfRangeException when it attempts to map to the nonexistent sixth value.

To Reproduce
Steps to reproduce the behavior:

Call InvokeAsync on a stored procedure using SqlProvider.MsSql
Get IndexOutOfRangeException before the procedure is called

Expected behavior
The stored procedure is called with the given values and the result set is retrieved.

Environment (please complete the following information):

Database: MsSql
Nuget Package and version: SQLProvider.MsSql 1.5.7
OS: Windows
.NET 10

Additional context
This error was introduced in my project when I upgraded from SqlProvider 1.3.19 to SqlProvider.MsSql 1.5.7. I don't know exactly what it was doing internally previously, but I would assume either the result set was not being created as an input parameter, or it was being created as an input parameter with a direction of ReturnValue which was being filtered out when creating command parameters. My naïve solution to this would be to also filter out Output direction, or perhaps only keep Input direction, though I don't know what that would do in other situations.

If it matters, I am using SaveContextSchema in order to allow the CI build to succeed without having to rely on a stable database and connection. Reviewing the generated schema, I only have one stored procedure which has a result set, which the direction of the ResultSet parameter is 2 instead of 1. All other stored procedures do not have a result set. I also have one function listed which has the first parameter unnamed with a direction of 6, as well as the last parameter with the name of ReturnValue also a direction of 6. I assume the direction of 6 is for return values.

The issue appears specifically to be a bug in SaveContextSchema. Placing a breakpoint at SqlDataContext.CallSprocAsync, when I am using a context schema, the def.Params has a length of 6, corresponding to the five input parameters as well as the ResultSet output parameter, while the retCols has a single parameter corresponding to the ResultSet output parameter. Meanwhile, if I delete the schema file and remove the schema options, def.Params has a length of 5 corresponding to the five input parameters without the ResultSet output parameter, where is is instead only the one entry in the retCols parameter. It appears that, either when it is saving the schema file, or when it is loading it afterwards, the def.Params is not being correctly processed.

It appears that it takes a few steps to properly reproduce the issue, as I was unable to reproduce it initially when I recreated a minimal setup. It appears that sprocParameters list in lambda passed to resultType.AddMembersDelayed in the DesignTimeUtils.generateSprocMethod will end up with different values depending on whether it was provided with an IDbConnection. If provided with one, the list will only contain the parameters and not the result set as a parameter based on the getSprocParameters function. When the SchemaCache is persisted to a file using the SaveContextSchema method, the result set parameter gets appended to the rest of the parameters when persisted. This is the list of parameters that is loaded when loaded as the ContextSchema. However, the original type provider assembly is still loaded, with an active IDbConnection, thus the project needs to be unloaded (possibly an entire IDE restart) and a rebuild afterwards in order for the ContextSchema to actually be used in a build. The ProvidedParameters for the Invoke and InvokeAsync are generated from the sprocParameters after filtering it out to only include Input and InputOutput directions. This filters out the result set parameter with the offline schema, while the result set parameter does not exist with the online schema, thus they match at this point. However, construction of the RunTimeSprocDefinition which is added as the first parameter to the generated call to CallSprocAsync ends up with an unfiltered sprocParameters, which is different depending on whether the SchemaCache is online or offline. When built online, the runtimeSproc.Params does not contain the result set parameter in the list, whereas when built offline (which requires unloading the type provider assembly after having created the context schema file so that it loads it in offline mode) the result set parameter is in the list. I've verified this by comparing the output of a decompiler with the assembly built in online vs offline mode.

Perhaps given that the ProvidedParameters list is generated by taking the sprocParameters list and keeping only those with a direction of Input or InputOutput, that ProvidedParameters determines what will be provided to CallSprocAsync for the values argument, that executeSprocCommandCommon requires that its filtered inputParameters and values lists match in size exactly, that inputParameters is provided as an unfiltered view of sprocParameters, and that currently executeSprocCommandCommon only filters out parameters with a direction of ReturnValue (resulting in parameters with direction Input, InputOutput, and Output), it might make sense that executeSprocCommandCommon be altered to use the same filtering that is done for ProvidedParameters so that the filtered inputParameters and values match in count, which is required in the mapi after the filter in order to prevent an IndexOutOfRangeException when the index value of the current filtered inputParameter is used to look up its corresponding values entry. I don't see this being a breaking change as the only people who would be affected by this change would already be broken. I also didn't look at any other providers to see if they would either need to implement the same solution, or if they perhaps already this solution or equivalent, or if they happen to persist the ContextSchema in a way that doesn't raise this issue.

Upon review of the other providers, I noticed that MSAccess throws an exception as it does not support stored procedures, SQLite only uses values and does not use inputParameters when attempting to make a PRAGMA statement from the stored procedure call, and all other providers filter inputParameters to only include parameters with direction of Input. Based on this, I think it would be reasonable to either also follow suit and filter keeping only Input instead of filtering out ReturnValue and treat the case where a stored procedure has an InputOutput parameter as an error case, or change the filter to keep Input and InputOutput so that it matches the list of ProvidedParameters. To me the latter makes the most sense, assuming SqlProvider works (or will work in the future) with stored procedures having parameters with a direction of InputOutput.

Fix for #877 IndexOutOfRangeException

Merge pull request #878 from Thorium/fixed-issue

I have built a local package containing the fix for this, and it appears to work properly for my case. Once a new release is created, this issue should be fixed.

1.5.28 should be there in 10 mins...

Navigation Menu

Repository navigation

IndexOutOfRangeException when invoking a stored procedure #877

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

IndexOutOfRangeException when invoking a stored procedure #877

Description

Activity

TheJayMann commented on Oct 14, 2026

TheJayMann commented on Oct 15, 2026

TheJayMann commented on Oct 15, 2026

TheJayMann commented on Oct 15, 2026

TheJayMann commented on Oct 17, 2026

Thorium commented on Oct 17, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions