Strange "Invalid column name" error when executing SP

Question

We have an SP in which we build a query and then execute the query by using the exec(@select) call, where the variable that holds the SQL query as it is constructed is @select.

I have been assigned a request to add a new column to the resultset returned by the SP. This new column should not be returned in all circumstances, but only under a certain condition. Naturally, I added the following code to the SP.

IF @conditionIsMet
BEGIN
set @select =  @select + ", 'compQty' = convert(varchar(53), di.qty) "
END

This worked fine until today, when my app keeps throwing a SQL exception with the message, "Invalid column name: compQty". This is erratic and the exception is not thrown always.

Executing the SP in SQL Server Management Studio gives no errors. The column is rendered with the heading 'compQty'. So the app should pick up the column, but it doesn't seem to!!

Can some one help?

Answer 1

Don't surround the column name in single quotes 'compQty'

Answer 2

It seems to me like Quoted Identifiers is the issue here.

It seems like a setting was changed in your environment for the quoted identifiers and that caused the exception. Use this at the beginning of your query:

Set Quoted_Identifier OFF;

Edit: You are sometimes thrown the issue and sometimes not because your IF condition is not always met.

Answer 3

Related to the Quoted Identifier problem that @Tapori identified: if the stored proc looks like:

CREATE PROCEDURE [dbo].[usp_Something]
AS
   SET QUOTED_IDENTIFIER OFF

   DECLARE @select NVARCHAR(200)

   SET @select = 'SELECT * FROM table WHERE ''col1'' = 42'
GO

Then you'll have a problem. The issue is that the setting of QUOTED_IDENTIFIER doesn't matter when the stored proc is executed, it matters when the stored proc is created. So you actually need to do:

SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[usp_Something]
AS
   DECLARE @select NVARCHAR(200)

   SET @select = 'SELECT * FROM table WHERE ''col1'' = 42'
GO

And see what that does.