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]

Re: Using IF inside SELECT ?

On 19 Apr 2006 07:07:47 -0700, Igor wrote:
[color=blue]
>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[/color]

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]

Re: Using IF inside SELECT ?

Am 19 Apr 2006 07:07:47 -0700 schrieb Igor:
[color=blue]
> 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[/color]

can be done this way:

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

bye, Helmut

[Hugo Kornelis]

Re: Using IF inside SELECT ?

On Wed, 19 Apr 2006 16:33:26 +0200, Hugo Kornelis wrote:
[color=blue]
>SELECT id
>FROM projects
>WHERE title LIKE @keyword
>AND ( @option <> 1 OR description LIKE @keyword )[/color]

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

--
Hugo Kornelis, SQL Server MVP