GOTCHA! QUOTED_IDENTIFIER usage for stored procedures

I spent almost half of my Friday looking for an elusive error in one of my stored procedures that, of course, was working on our development environment. It led me to discovering something I was unaware of in regards to how SQL Server evaluates QUOTED_IDENTIFIER.

The error I was getting was stating my QUOTED_IDENTIFIER flag was OFF for an xml parse call. See below for the snippet from the stored procedure.

SET QUOTED_IDENTIFIER ON
SELECT @xml = CAST(@xml_message AS XML);
SELECT @entity_id = @xml.value('(/parent/entityid)[1]','float');

Thankfully, consulting MSDN revealed that, in fact, setting QUOTED_IDENTIFIER within your stored procedure does not change how the proc is executed. It stores the QUOTED_IDENTIFIER setting from the creation of the proc.

SET QUOTED_IDENTIFIER Documentation (MSDN)

When a stored procedure is created, the SET QUOTED_IDENTIFIER and SET ANSI_NULLS settings are captured and used for subsequent invocations of that stored procedure.

When executed inside a stored procedure, the setting of SET QUOTED_IDENTIFIER is not changed.