How to pass comma separated list in sp_executesql

Question

I want to use IN opeartor in sp_executesql, but facing the error that Incorrect syntax near '@TagIndexListToAdjust'.

This error is due to single quotes at both side of the parameter value '(1,2,3)'.

I need to fix it with in only the sp_executesql as this query is generated by C# model class.

 USE [master]
    GO
    IF  EXISTS (SELECT * 
                FROM sys.objects
                WHERE object_id = OBJECT_ID(N'[dbo].[Persons]') AND type in (N'U'))
    DROP TABLE [dbo].Persons
    GO
    USE [master]
    GO
    CREATE TABLE Persons
    (
          commaList nvarchar(MAX),
    );
    insert into Persons values ('1')
    insert into Persons values ('2')
    insert into Persons values ('3')

    GO

    exec sp_executesql N'

    Select * 
    from Persons 
    where commaList in @TagIndexListToAdjust',
          N'@TagIndexListToAdjust varchar(67)',
          @TagIndexListToAdjust='(1,2,3)'

Any help will be appriciated in fixing the error.

Answer 1

Use 2 quotation marks.. one for escaping the other one.