C# and SQL and Dynamics GP: Sending a List to a Stored Procedure

By Steve Endow

This is a discussion of how to send a list of values to a SQL stored procedure parameter.

Specifically, how can a .NET application send a comma delimited list of values to a stored procedure to be used in an "IN" operator in a WHERE clause.

Using a parameter with a WHERE clause IN operator

TL;DR: If you want to jump straight to the solution, read this excellent post on Arrays and Lists in SQL Server by legendary SQL Server MVP Erland Sommarskog.

If you crave to learn about all of the exciting details, read on...



I'm working on adding support for Binary Stream Multi-Entity Management (MEM) to the free Dynamics GP Transaction Search tool.  To provide MEM support, I've added a checked list box to an MEM version of the Transaction Search window.

Entities listed in a checked list box

The user can select or unselect Entities in the list, and then perform a search.

When the search is run, the window builds a list of the currently selected Entities as a comma delimited string.

Loop through checked items and build a comma delimited string

Note that I initially tried putting apostrophes around each value, thinking that would allow SQL to see the values as separate char values--but obviously that didn't work and with the solution below, the apostrophes were unnecessary.

After that list of entities is generated, the application calls a stored procedure, sending in the Selected Entities in as a parameter.

Selected Entities list is submitted as a single varchar parameter

And as shown previously, I needed to use the Selected Entities in a WHERE IN clause.

But, of course, this didn't work.

When the single string of Selected Entities is submitted to the stored procedure, SQL only sees a single value.

           WHERE P.Entity IN ('<long string with commas and apostrophes>')

And no records will be returned by the stored procedure.

So how do we convert this comma delimited list to something that can be used in a WHERE IN clause?

I found a few posts online that discussed this common challenge and offered what appeared to be rather inelegant solutions. Some used User Defined Functions, others used various looping techniques to try and reconstruct the list of values.

I then found this fantastic discussion of the issue by SQL Server MVP Erland Sommarskog. In the post, he discusses the background of the IN operator and offers a very simple and elegant solution, with a discussion of limitations and performance implications.

He does recommend considering Table Valued Parameters, but I opted for the simpler approach and used the SQL string_split function.

Based on his article, I came up with this:

Using string_split to convert list to 

This worked perfectly!

Testing the stored procedure, I am able to send in a single comma delimited string to the @SelectedEntities parameter and get valid results from the procedure.

EXEC dbo.csspPMTransactionSearchMEM 
            @StartDate = '2020-01-01',
            @EndDate = '2030-12-31',   
            @DocNumber = '', 
            @VendorID = '',
            @VendorName = '',
            @AmountFrom = 0,
            @AmountTo = 10000000000,
            @SelectedEntities = '100,200,300,400,999'


But then I read with sadness that the string_split function is only available in SQL Server 2016 and higher.  Anyone who works with Dynamics GP knows that many GP customers are still running older versions of SQL Server, and that a general Dynamics GP solution must be designed to work with those older versions of SQL Server.

So, sadly, I had to use the intlist_to_tbl User Defined Function that Erland provided, and I modified the stored procedure to use that function instead.  But I did include the option that uses string_split in case a customer does prefer to use that with a newer version of SQL Server.

Include support for both string_split and the UDF for older SQL versions

One last note.

Notice that SQL Server Management Studio displays a green line underneath the line with the IN operator.

When I hover over the line, it recommends that I "Consider using EXISTS instead of IN".

I researched this briefly, and while I found several comments indicating that EXISTS can result in better performance, there were several other comments indicating that the SQL Server query optimizer will review the query, detect the structure, and produce the same query execution plan for either IN or EXISTS.

I don't have time to explore that debate at the moment, so I've just used IN for now.

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics GP, Dynamics 365 Business Central, SQL Server, .NET, Microsoft Flow, PowerApps, and Azure.

You can also find him on Twitter and YouTube

http://www.precipioservices.com