SQL query with dynamic parameters

I tried to get some data from DB. I need get account information from my table – this type of information will depend on User. If he/she will insert account type = 1 -> he will get this information. How could I create query if it will depend on user: for example user could choose one or more account types and how would show my query? Now I have something like:

SELECT accountID, accountName,AccountNo,accountType from account_ref
where accountType not in (4, 9) and accountType IN (1, 2, 3)

But account type could be one or not.

EDITED: Okey, If I try to set this property from my program (it is c#) In this case I created method: input parameters – it is list with AccountType(Which user turned) How could I set it dynamically

        public async Task<IList<AccountInfo>> GetAccountByAccountType(IList<AccountType> item)
        {
            //item

            string query = $"SELECT accountID, accountName, AccountNo, accountType from account_ref " +
                            "where accountType not in (4, 9) and accountType IN(1, 2, 3)";

            var accType = await Connection.QueryAsync<AccountInfo>(query);

            return accType.ToList();
        }

Answer

You can use IN with comma sepearted values

DECLARE @ids NVARCHAR(100) ='1,2,3'    
SELECT accountID, accountName,AccountNo,accountType 
FROM account_ref
WHERE accountType NOT IN (4, 9) 
AND accountType IN (SELECT value from STRING_SPLIT (@ids, ','))

Advertisement

Answer