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
– Drew, Commented Dec 16, 2015 at 5: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), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoid sp_ and use something else as a prefix - or no prefix at all! — marc_s
– marc_s, Commented Dec 16, 2015 at 5:56

marc_s · Accepted Answer · 2015-12-16 05:57:04Z

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, 2015 at 5:57

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 16, 2015 at 4:39

Mukesh Kalgude

4,8542 gold badges21 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pcs Over a year ago

what about searchclass?

Community · Accepted Answer · 2017-05-23 12:31:05Z

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.

Dean · Accepted Answer · 2015-12-16 05:31:08Z

0

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.

answered Dec 16, 2015 at 5:31

Dean

68813 silver badges34 bronze badges

Comments

HiteshAjudiya · Accepted Answer · 2015-12-16 05:33:25Z

0

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

answered Dec 16, 2015 at 5:33

HiteshAjudiya

1374 bronze badges

Collectives™ on Stack Overflow

How to add multiple conditions in sql?

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related