Procedure plan not cached when MAX parameter is concatenated to - by Erland Sommarskog

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


7
0
Sign in
to vote
ID 473880 Comments
Status Closed Workarounds
Type Bug Repros 1
Opened 7/11/2009 8:00:07 AM
Access Restriction Public

Description

You have a stored procedure which have these characteristics:
1) The procedure has a parameter of the type nvarchar(MAX), call it @p
2) There is a SELECT from a table in the procedure.
3) In the SELECT there is an expression like @p + ','
Under these cicrumstances the procedure will not be put into the procedure
cache.

This may seem like a small thing, but if you use a procedure that uses
a function like inline_split_me to crack a comma-separated list into a table,
you are exposed to this behaviour, which could be costly in a multi-user
environment, because of constant recompile.

See http://www.sommarskog.se/arrays-in-sql-2005.html#tblnum for the 
function inline_split_me.

Sign in to post a comment.
Posted by Microsoft on 4/2/2010 at 6:43 PM
I am sorry for the confussion. The bug is fixed in the next version of SQL Server but we are not going to make a fix for SQL Server 2008. The "Won't fix" was only for the SQL Server 2008 part.

Thank you,
Ciprian Clinciu
Posted by Erland Sommarskog on 3/20/2010 at 2:28 AM
You first said you have fixed it, then you close it as Won't Fix???? Could you please clarify?
Posted by Sangeetha [MSFT] on 7/14/2009 at 11:07 AM
Thank you for bringing this to our attention. This issue has been fixed in the next release. Based on your feedback, we will triage it for SQL 2008 as well.

Thanks,
Sangeetha