sp_executesql limitations

Posts   
 
    
kakaiya
User
Posts: 159
Joined: 20-Mar-2004
# Posted on: 13-Feb-2021 01:33:39   

Hi,

Using LLBLGen, it generates the below query. Does the 2100 parameters limit apply to sp_executesql?

WHERE  ([PMSPROD].[dbo].[pmsFund].[funFundId] IN (104 /* @p1 */, 105 /* @p2 */, 106 /* @p3 */, 107 /* @p4 */,
                                                  108 /* @p5 */, 109 /* @p6 */, 110 /* @p7 */, 111 /* @p8 */,
..........................
..........................
928 /* @p825 */, 929 /* @p826 */, 930 /* @p827 */, 931 /* @p828 */,
                                                  932 /* @p829 */, 933 /* @p830 */, 934 /* @p831 */, 935 /* @p832 */)

Thank you.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 13-Feb-2021 08:50:29   

yes, it's a limitation in the ADO.NET provider. A future Microsoft.Data.SqlServer provider will lift this limitation btw.

sp_executesql is unavoidable, it's always used when executing a query through SqlCommand.

Frans Bouma | Lead developer LLBLGen Pro
kakaiya
User
Posts: 159
Joined: 20-Mar-2004
# Posted on: 13-Feb-2021 09:31:49   

Hi Otis,

Thank you.

For cs sp_executesql Some SQL WHERE Code IN (1,2,3)is count as 1 parameters or 3 parameters?

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39590
Joined: 17-Aug-2003
# Posted on: 14-Feb-2021 10:11:00   

None actually, as you haven't specified any parameters. WHERE ... IN (@p1, @p2, @p3) counts as 3. (And ignore sp_executesql, you can't avoid that)

Frans Bouma | Lead developer LLBLGen Pro
kakaiya
User
Posts: 159
Joined: 20-Mar-2004
# Posted on: 15-Feb-2021 14:52:26   

Otis wrote:

None actually, as you haven't specified any parameters. WHERE ... IN (@p1, @p2, @p3) counts as 3. (And ignore sp_executesql, you can't avoid that)

Hi Otis,

Thank you.