How to add multiple conditions in sql?

Question

Here is my stored procedure:

ALTER PROCEDURE sp_searchedstudentrecords
(
@condition varchar(20),
@searchtext varchar(50),
@searchclass int


)
AS
begin
If (@condition = 'startswith')
select * from student where name like @searchtext+ '% '
else if (@condition = 'endswith')
select * from student where name like '%' +@searchtext
else
select * from student where name like '%' +@searchtext+ '%'
End

I have been added extra one text box for search by class with the above query.

@searchclass int

May i now what should be the query for search by class look like?

I'm new sql query and .net,

I have been created dropdown contains startswith,endswith and contains and one text box for search by name.

I already done select record where search by name, with this i need to select record from table where search by class.

Can anyone guide me for query?

Thanks,

well frankly I don't even know why you are using a stored proc. Use the power of your front end language and have a Query Builder. I wouldn't use tsql for concats because it is not as powerful, and as your needs augment, it is not the place for it. So just do it in c# and be done with it — Drew, Dec 16 '15 at 05:16
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Dec 16 '15 at 05:56

score 3 · Answer 1 · edited Dec 16 '15 at 05:57

3

Like this

select * 
from student 
where name like (case 
                    when @condition = 'startswith' then  @searchtext+ '% ' 
                    else 
                        case when @condition = 'endswith' then '%' + @searchtext 
                             else '%' + @searchtext + '%' 
                        end 
                 end) 
 and studentid = isnull(@searchclass, studentid)

Above query to studentid has been extra search

If any id to search passing value other wise pass null This query should be given result.

edited Dec 16 '15 at 05:57

marc_s

675,133
158
1,253
1,388

answered Dec 16 '15 at 04:39

Mukesh Kalgude

4,626
1
15
31

what about searchclass? – pcs Dec 16 '15 at 05:02

score 2 · Answer 2 · edited May 23 '17 at 12:31

As already suggested, it is better to do this in your front-end by constructing a parameterized query as described here. Since you cannot use the protective power of parameterized with LIKE, you must be careful to sanitize your string (avoid Sql injection).

When implementing in .NET, it is best practice to avoid string comparisons. You can define your filters as a fixed list of id->name pairs.

enum LikeFilterType
{
   Startswith = 1,
   Endswith = 2,
   Inside = 3
   // maybe exactly can be added later
}

IList<SelectListItem> FilterOptions = new List<SelectListItem>()
   {
      new SelectListItem() { Value = (int) LikeFilterType.Startswith, Text = "Starts with" },
      new SelectListItem() { Value = (int) LikeFilterType.Endswith , Text = "Ends with" },
      new SelectListItem() { Value = (int) LikeFilterType.Inside, Text = "Inside" }
   }

Your combo may "bind" directly to this list and use selected value, not the string in your logic (what you display may change over time, you do not want to change logic when changing a displayed name!)

Coding in ASP.NET allows to have a more readable and maintainable code.

NOTE: keep in mind that user should know or will be affected by LIKE special characters.

score 0 · Answer 3 · answered Dec 16 '15 at 05:31

I assuming your parameters are nullable, therefore you can just do like this

SELECT * FROM student 
WHERE name LIKE ( CASE WHEN @condition = 'startswith' THEN @searchtext + '%'
                       WHEN @condition = 'endswith'   THEN '%'+@searchtext
                       ELSE '%'+@searchtext+'%'
                  END
                ) AND
      (@searchclass IS NULL OR studentClass = @searchclass)

@searchclass IS NULL is for you to check is the parameter's value and it wont clash even if your @searchclass is NULL

Hope it help ya.

score 0 · Answer 4 · answered Dec 16 '15 at 05:33

Refer below updated stored procedure with Class filter.

Change Class column based on exact column name:

ALTER PROCEDURE sp_searchedstudentrecords
(
@condition varchar(20),
@searchtext varchar(50),
@searchclass int


)
AS
BEGIN
    IF (@condition = 'startswith')
        SELECT * FROM student WHERE  name like @searchtext+ '% ' AND Class = @searchclass
    ELSE IF (@condition = 'endswith')
        SELECT * FROM student WHERE  name like '%' +@searchtext  AND Class = @searchclass
    ELSE
        SELECT * FROM student WHERE name like '%' +@searchtext+ '%' AND Class = @searchclass
END

How to add multiple conditions in sql?

4 Answers4