If Else Statement in SQL Server stored procedure

Question

In SQL Server, on .NET you can add together a SQL statement and therefor use either 'or' or 'and'.

I am stuck how to do this in a SQL Server stored procedure.

Question:

What is it called what you add together SQL statements? I have a feeling it starts with a 'c' concocations??
I believe I am close with the below code to having a 'variable' SQL?

Code:

BEGIN
    SELECT * 
    FROM TblEmployee
    WHERE 
       FirstName LIKE  '%' + LTRIM(RTRIM((@sFirstName))) + '%'   
       or
       Surname LIKE '%' + LTRIM(RTRIM((@sSurname ))) + '%'   
       and
       IF (LTRIM(RTRIM((@sOfficeBase))) = 'xyz1234') 
       and OfficeBase = LTRIM(RTRIM((@sOfficeBase)))
       ELSE
       or
       OfficeBase= LTRIM(RTRIM((@sOfficeBase)))
END

There are simular queries such as If else in stored procedure sql server

But I have a feeling I searching for the 'wrong' question. Thanks in advance

concatenation (Although this does not really describe IF/ELSE branches) — Alex K.
– Alex K., Commented Nov 14, 2014 at 13:30
ELSE OR / IF AND are nonsensical, the format is IF/ELSE <condition> [OR/AND ...] perhaps you had better describe the logic you want in words as its hard to interpret what you have shown. — Alex K.
– Alex K., Commented Nov 14, 2014 at 13:31
I want to add to the SELECT statement based on a value from the user. If the value matches something I want the SELECT statement to add an 'and x = y' statement otherwise it should be an 'or' statement. — indofraiser
– indofraiser, Commented Nov 14, 2014 at 14:07
The IF ELSE construct is used to control flow of statements inside a procedure. It is not used to handle logic inside of a query. — Sean Lange
– Sean Lange, Commented Nov 14, 2014 at 14:47
@SeanLange how can I 'create' a stored procedure statement based on input from a user then. I thought there was some conditional formatting that could be done within the stored procedure — indofraiser
– indofraiser, Commented Nov 14, 2014 at 14:52

Neville Kuyt · Accepted Answer · 2014-11-14 14:54:17Z

2

The where clause allows you to embed conditional logic - you don't need if/then/else, which is just as well, because afaik, that's not supported by any SQL dialect in a where clause.

BEGIN
    SELECT * 
    FROM TblEmployee
    WHERE 
       FirstName LIKE  '%' + LTRIM(RTRIM((@sFirstName))) + '%'   
       or
       Surname LIKE '%' + LTRIM(RTRIM((@sSurname ))) + '%'   
       and
       (OfficeBase = 'xyz1234' 
       or
       OfficeBase= LTRIM(RTRIM((@sOfficeBase)))
END

edited Nov 14, 2014 at 14:54

answered Nov 14, 2014 at 13:38

Neville Kuyt

29.6k2 gold badges40 silver badges52 bronze badges

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

2 Comments

indofraiser Over a year ago

Just to clarify that will only 'use' the 'and' statement if the value of @sOfficeBase is 'xyz1234' otherwise it will use the 'or' statement?

Neville Kuyt Over a year ago

Just re-reading it - the office base comparison in the first bit of the brackets was redundant, so I've removed it. Should be easier to read - the brackets work as they would in any language.

Sean Lange · Accepted Answer · 2014-11-14 16:05:40Z

I am pretty sure you have some missing parenthesis in your original query. Not sure why you are adding LTRIM and RTRIM to your parameters. Taking a shot in the dark I suspect you could boil down your query to this.

SELECT * --This should be the column names actually needed, * is not good for production code
FROM TblEmployee 
WHERE 
(
    FirstName LIKE  '%' + @sFirstName + '%'   
    or
    Surname LIKE '%' + @sSurname + '%'   
)
and OfficeBase in ('xyz1234', @sOfficeBase)

Collectives™ on Stack Overflow

If Else Statement in SQL Server stored procedure

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related