114 reputation
4
bio website tadmas.com
location College Station, TX
age 33
visits member for 2 years, 5 months
seen Dec 14 '12 at 19:08

I'm a web developer on a WISC platform, but I dabble in Win32, C/C++, and yaws/erlang.


Jul
16
awarded  Supporter
Jul
14
comment Stored procedure compilation blocking due to many parameters to sp_executesql
@AaronBertrand Even with all the parameters on the outer procedure, as long as I shortened the parameters on the sp_executesql call, it cached the plan.
Jul
14
awarded  Editor
Jul
14
revised Stored procedure compilation blocking due to many parameters to sp_executesql
change datatypes to make it clear the recurring varchar(100) was not a pattern, but was rather due to my laziness in anonymizing the procedure
Jul
14
comment Stored procedure compilation blocking due to many parameters to sp_executesql
I heavily edited the procedure in the attempt to make the example more clear. The app only passes some of the parameters. I left in the dynamic WHERE clause trying to show how they were used: optional filters. Similarly, the fact that they are all varchar(100) is just due to copy/paste to get to 4000 characters; they are actually different data types.
Jul
14
comment Stored procedure compilation blocking due to many parameters to sp_executesql
I probably shouldn't have made all the types the same. They are wildly different: ints, datetimes, decimal, money, etc. It was just easier to copy & paste varchar(100) over and over. I chose 100 because it was 3 digits and made it easier to get to 4000 characters. :)
Jul
13
comment Stored procedure compilation blocking due to many parameters to sp_executesql
Yeah, that's probably what we're going to have to do. I am concerned since the real parameter list is well over 4000 characters (somewhere around 5800) and keeps growing as more functionality is needed. At some point the list of data types might overflow the size by themselves.
Jul
13
awarded  Student
Jul
13
asked Stored procedure compilation blocking due to many parameters to sp_executesql
Jul
13
awarded  Autobiographer