Dynamic parameters to stored procedure

I have a problem in creating dynamic parameters to stored procedure. I have two nvarchar parameters in a stored procedure that are combination of words separated with comma. For example:

    'Login, check, tak开发者_JS百科e, return' and 'admin, yes, no, yes' 

I have to include these parameters in the where clause like

    login=admin and check=yes and take=no and return=yes

The problem is that the number of words in the parameters are dynamic. It can vary from 1 to 20 words.

Is there any solution to handle this?

• Define 20 parameters with defaults of NULL
• Only set the ones you want
• In the WHERE clause do (@Param IS NULL or Column = @Param)

If you have completely dynamic SQL and random parameter names then it's one of

• you shouldn't be using stored procedures
• you need to write more stored procedures for different use cases

You wouldn't have a completely dynamic .net method would you?

You have to create dynamic SQL Query inside your stored procedure. In stored procedure you have to split your parameter and value and built dynamic where condition.

You have to save in the variable in stored procedure.

E.g.

@sql as nvarchar(1000)
@sql = 'select * from tablename where'

Now you Loop through ur variable like

@sql =  @sql +' '+ @firstvariable + ' = '''+ @valuevariable +'' AND '