Sample Header Ad - 728x90

quoted identifiers on filtered indices

0 votes
1 answer
384 views
I´ve 2 questions. I am using a T-SQL Server 2014. When creating a table with a filtered index on a T-SQL server, it is a must-have to set QUOTED_IDENTIFIER to ON. Why is this so? I´ve some SPs which have set quoted_identifiers to OFF, my question is, if I call the SP with QUOTED_IDENTIFIER ON, is it possible to inject these with values which have doublequotes inside? The SP makes an insert and just uses the parameters. As I understand it, it should not take an effect if QUOTED_IDENTIFIER is set to ON or OFF. This is my first SP which calls the second: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO DECLARE @PreviousVersion bigint IF @EntryTime IS NULL BEGIN SET @EntryTime = GETUTCDATE() END IF @ID = 0 BEGIN EXEC dbo.GetNextDataID 'Object', @ID OUTPUT SET @PreviousVersion = 0 END ELSE BEGIN SET @PreviousVersion = @Version END EXEC dbo.GetNextVersion 'Object', @Version OUTPUT EXEC dbo.Insert_Object @ID, @Version, @PreviousVersion, @deleted, @Parent The second looks like this: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO SET NOCOUNT ON; IF ISNULL(@ID, 0) = 0 BEGIN RAISERROR('Die ID ist 0 oder leer.', 11, 1) RETURN END IF ISNULL(@Version, 0) = 0 BEGIN RAISERROR('Die Version ist 0 oder leer.', 11, 2) RETURN END IF @EntryTime IS NULL BEGIN SET @EntryTime = GETUTCDATE() END UPDATE dbo.ObjectTable SET ID = @ID INSERT INTO dbo.ObjectTable ( ID , Version , PreviousVersion , deleted , Parent) VALUES ( @ID , @Version , @PreviousVersion , @deleted , @Parent) If I add the filtered index without setting QUOTED_IDENTIFIER to ON, it raises an error.
Asked by Robert (125 rep)
May 29, 2020, 10:28 AM
Last activity: May 29, 2020, 10:50 AM