Merge duplicate multiple result sets

[sjh37]

If a stored procedure contains conditional statements, such as this:

IF(@someVar < @otherVar) BEGIN
   SELECT a,b,c WHERE blah
ELSE
   SELECT a,b,c WHERE blah OR blah
END

The result sets will be the same, even though two can be returned. Instead of including ResultSet1 and ResultSet2, these can be merged and instead use a single return model.

[youngcm2]

+1

[youngcm2]

Or it would be nice to specify in the tt to only return 1 result set for procs.

[jvinkovic]

Any update on this?? Or, can be specified in tt to include only first one?

[aplocher]

+1

[jvinkovic]

I resolved it in a way that always first set is taken if specified in tt file. Will make a pull request in a month.

[redmeadow]

Any updated workaround for this? The only thing I've found to work reliably is to just separate my stored procedure that contains IF/ELSE SELECTs into separate stored procedures that map to the same result set entity, but that's not ideal...

[sjh37]

I fully understand. It's actually SQL Server that tells me what will be returned, and where there are conditionals, for some reason it tells me there will be two or more result sets returned, even though in reality there could be only one.

As some results sets returned may be identical, they could in fact contain valid data. So what will happen is I will add an array to the settings you can edit so you can opt in certain stored procs to be merged if you know that is safe to do so.

Time scales - This is low priority, and will happen sometime after I have completed adding support for EF .Net Core version. Another biggie, is to add support for Oracle, MySql, Postgres, etc. However, I don't mind injecting quick wins into the generator, which this one would be. However, I've promised everyone that I'm concentrating on EF core support.

[redmeadow]

Understood, thanks for the quick reply. And I realize that you're just dealing with whatever SQL Server tells you about the stored procedure. It's unfortunate that SQL Server appears to be wrong in this case. The setting addition to merge result sets for specific stored procedures would be great. In the meantime, at least there are some other ways to workaround the issue.

[CorsairRO]

hello,

please find my temporary solute that I presented in Issue #265

[sjh37]

I have included a new setting flag which defaults to true. This will cause the generator to inspect multiple result sets to see if they are all identical. If they are all identical the duplicates will be removed leaving a single model.

Settings.MergeMultipleStoredProcModelsIfAllSame = true;

This will be in the next release.