Dynamic query 'in clause' parameter not working

Question

I am using dynamic query in stored procedure. I passed two parameters one for "equal case" and the other for "in case". The in case not working while the equal case is working as shown in the images. Is there any way that I can put the parameter for "in case" in query.

Here is my procedure for better editing.

    Alter PROCEDURE [dbo].[Test_In_Clause]

    -- Add the parameters for the stored procedure here
    @name nvarchar(50) = NULL,
    @class nvarchar(50) = NULL


AS
BEGIN

    declare 
    @sql nvarchar(max),
    @ParameterDef NVARCHAR(500)

    set @ParameterDef = '@name nvarchar(50),
                        @class nvarchar(50)'

    set @sql  = 'Select * from aaa_Students where Name = @name and Class in @class'
    print @sql
    exec sp_Executesql @sql, @ParameterDef, @name = @name, @class = @class


END

PS: I don't want to use Select * from aaa_Students where Name = @name and Class in ('+ @class +') format in order to protect sql injection.

Answer 1

Try this:

    alter PROCEDURE [dbo].[Test_In_Clause]

    -- Add the parameters for the stored procedure here
    @name nvarchar(50) = NULL,
    @class nvarchar(50) = NULL


AS
BEGIN

    declare 
    @sql nvarchar(max),
    @ParameterDef NVARCHAR(500)

    set @ParameterDef = N'@name nvarchar(50),
                        @class nvarchar(50)'

    set @sql  = 'Select * from aaa_Students where Name = @name and Class in (@class)'
    print @sql
    execute sp_Executesql @sql, N'@name nvarchar(50),@class nvarchar(50)', @name = @name, @class = @class