CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresdynamic-sqlin-clause
Soe Ya Htet
Soe Ya Htet

Reputation: 31

Dynamic query 'in clause' parameter not working

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.

Stored Procedure

Executing stored procedure

Upvotes: 1

Views: 1152

Answers (1)

zip
zip

Reputation: 4061

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

Upvotes: 2

Related Questions