Using IF inside SELECT ?

[Igor]

Is there possibility to use IF conditions inside SELECT statements?

For example, can i write something like this:
CREATE PROCEDURE [search]
(
@OPTION int,
@KEYWORD nvarchar(40)
)
AS
BEGIN
SELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)
THEN (OR description LIKE @KEYWORD)
END

or am i limited to this:
....
BEGIN
IF @OPTION = 1
SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE
@KEYWORD
ELSE
SELECT id FROM projects WHERE title LIKE @KEYWORD
END

[Hugo Kornelis]

On 19 Apr 2006 07:07:47 -0700, Igor wrote:

Is there possibility to use IF conditions inside SELECT statements?

For example, can i write something like this:
CREATE PROCEDURE [search]
(
@OPTION int,
@KEYWORD nvarchar(40)
)
AS
BEGIN
SELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)
THEN (OR description LIKE @KEYWORD)
END

or am i limited to this:
...
BEGIN
IF @OPTION = 1
SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE
@KEYWORD
ELSE
SELECT id FROM projects WHERE title LIKE @KEYWORD
END

Hi Igor,

The latter will probably perform best. But if you prefer to have it in
one query, you can also use

SELECT id
FROM projects
WHERE title LIKE @keyword
AND ( @option <> 1 OR description LIKE @keyword )

For more complex cases, check out the CASE expression in Books Online.

--
Hugo Kornelis, SQL Server MVP

[Helmut Woess]

Am 19 Apr 2006 07:07:47 -0700 schrieb Igor:

BEGIN
IF @OPTION = 1
SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE
@KEYWORD
ELSE
SELECT id FROM projects WHERE title LIKE @KEYWORD
END

can be done this way:

SELECT id FROM projects WHERE title LIKE @KEYWORD OR
(@OPTION = 1 AND description LIKE @KEYWORD)

bye, Helmut

[Hugo Kornelis]

On Wed, 19 Apr 2006 16:33:26 +0200, Hugo Kornelis wrote:

SELECT id
FROM projects
WHERE title LIKE @keyword
AND ( @option <> 1 OR description LIKE @keyword )

Oops, I misread the question. Use Helmut's version instead.

--
Hugo Kornelis, SQL Server MVP