How to allow multiple values to be entered in a search filter (a textbox). and what will be the impact on performance

Question

There is a new enhancement in my project, i need to add a new textbox filter in which i have to allow multiple values to be entered.

obviously i am using "like" for a single valued parameter in my current procedure.

WHERE (@_adFirstName IS NULL OR firstName LIKE @_adFirstName + '%')
        AND (@_adLastName IS NULL OR lastName LIKE @_adLastName + '%')

later i need to use "IN" for multiple values. what will be the impact of performane of procedure in this scenario.

should be minimal, you code will be cleaner if you just run the same search x times though — Ewan, May 06 '15 at 08:04

score 1 · Answer 1 · edited May 23 '17 at 12:21

1

you didn't specify your sql server version, but since 2008 sql server supports table valued parameters. I find this to be the most effective way of passing a list of variables to a stored procedure.

You can find a complete description on how it's done in [my answer to another question here.] (How to pass string array in SQL parameter to IN clause in SQL)

edited May 23 '17 at 12:21

Community

1
1

answered May 06 '15 at 08:35

Zohar Peled

79,642
10
69
121

1

[Here is tge link to msdn about this](https://msdn.microsoft.com/en-us/library/bb510489.aspx). Sorry for not including it in my answer, i wrote it on my cellphone and paste is making a mess. – Zohar Peled May 06 '15 at 08:37

How to allow multiple values to be entered in a search filter (a textbox). and what will be the impact on performance

1 Answers1