IF Statement in WHERE condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

IF Statement in WHERE condition

Author

Topic

cool_moon
Starting Member

26 Posts

Posted - 2007-11-14 : 01:00:41

Hi,

Is it possible to put IF condition inside SELECT query after WHERE statement. What i am trying to archive is, if there is any value pass to a store procedure then only WHERE statement execute else bypass that and show all.

Following is the store procedure for reference. Here i'm checking if there is any "@Channel" value, if there is then execute "Program.channel_name in (' + @Channel +')" else bypass this and go to next condition. But if i run it it give me this error:

Incorrect syntax near the keyword 'AND'.

Any idea how can we archive this..?

Many Thank
Nebil

==================== Start ======================

CREATE PROCEDURE [dbo].[getProgramGeneralSearch]
@GenreCode varchar(20),
@Channel varchar(50),
@Keyword varchar(500),
@TimeAheadOfGMT int,
@RegionalizedStartTime smalldatetime,
@RegionalizedEndTime smalldatetime,
@ProgramTableName varchar(255)

AS
DECLARE @GmtStartTime smalldatetime
DECLARE @GmtEndTime smalldatetime
DECLARE @GenreCodeExist varchar(255)

SET @GmtStartTime = DATEADD(HH, -(@TimeAheadOfGMT), @RegionalizedStartTime)
SET @GmtEndTime = DATEADD(HH, -(@TimeAheadOfGMT), @RegionalizedEndTime)
SET @GenreCodeExist = @GenreCode

Execute
('
SELECT
Program.Id Id,
Program.Genre,
Program.Channel_name ChannelCode,
DATEDIFF(minute, 0, Program.Duration) Duration,
Program.Title TitleEn,
Program.Synopsis SynopsisEnglish,
(DATEADD(hh, ' + @TimeAheadOfGMT + ', Program.DateTime)) as RegionalizedStartTime,
(DATEADD(hh, ' + @TimeAheadOfGMT + ', (Program.DateTime + Program.Duration))) as RegionalizedEndTime
FROM
' + @ProgramTableName + ' Program
WHERE
(
(
Program.DateTime > ''' + @GmtStartTime + ''' AND
Program.DateTime < ''' + @GmtEndTime + '''
) OR
(
(Program.DateTime + Program.Duration) > ''' + @GmtStartTime + ''' AND
(Program.DateTime + Program.Duration) < ''' + @GmtEndTime + '''
)
)

IF( '+ @Channel +' IS NOT NULL)
BEGIN
AND
Program.channel_name in (' + @Channel +')
END
AND
Program.Genre in (' + @GenreCode +')
AND
Program.Title Like ''%'+ @Keyword +'%''

ORDER BY
Program.DateTime
')
GO

==================== Start ======================

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-14 : 01:01:44

You can't use IF .. ELSE inside a query. Use CASE .. WHEN .. END syntax.

KH
[spoiler]Time is always against us[/spoiler]

cool_moon
Starting Member

26 Posts

Posted - 2007-11-14 : 01:21:06

Thank KH for quick reply...yup just tried using CASE and now it's giving me this error:

Server: Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'CASE'.

here is the latest SP:

=============================

CREATE PROCEDURE [dbo].[getShowGuideGeneralSearch]
@GenreCode varchar(20),
@Channel varchar(50),
@Keyword varchar(500),
@TimeAheadOfGMT int,
@RegionalizedStartTime smalldatetime,
@RegionalizedEndTime smalldatetime,
@ProgramTableName varchar(255)

AS
DECLARE @GmtStartTime smalldatetime
DECLARE @GmtEndTime smalldatetime
DECLARE @GenreCodeExist varchar(255)

SET @GmtStartTime = DATEADD(HH, -(@TimeAheadOfGMT), @RegionalizedStartTime)
SET @GmtEndTime = DATEADD(HH, -(@TimeAheadOfGMT), @RegionalizedEndTime)
SET @GenreCodeExist = @GenreCode

Execute
('
SELECT
Program.Id Id,
Program.Genre,
Program.Channel_name ChannelCode,
DATEDIFF(minute, 0, Program.Duration) Duration,
Program.Title TitleEn,
Program.Arab_Title TitleAr,
Program.Synopsis SynopsisEnglish,
Program.Arab_Synopsis SynopsisArabic,
(DATEADD(hh, ' + @TimeAheadOfGMT + ', Program.DateTime)) as RegionalizedStartTime,
(DATEADD(hh, ' + @TimeAheadOfGMT + ', (Program.DateTime + Program.Duration))) as RegionalizedEndTime
FROM
' + @ProgramTableName + ' Program
WHERE
(
(
Program.DateTime > ''' + @GmtStartTime + ''' AND
Program.DateTime < ''' + @GmtEndTime + '''
) OR
(
(Program.DateTime + Program.Duration) > ''' + @GmtStartTime + ''' AND
(Program.DateTime + Program.Duration) < ''' + @GmtEndTime + '''
)
)
CASE('''+ @Channel +''')
WHEN '''' THEN
AND Program.channel_name in (' + @Channel +')
END

AND
Program.Genre in (' + @GenreCode +')
AND
Program.Title Like ''%'+ @Keyword +'%''

ORDER BY
Program.DateTime
')
GO

=================

I'm sure that i am doing some stupid mistake :S

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-14 : 01:51:53

1. Why dynamic SQL ? I don't see a need to use that in your query. Better read this http://www.sommarskog.se/dynamic_sql.html
2. If you are using dynamic SQL you should use sp_executesql instead of exec(). sp_executesql allows you to pass in parameter to the query.
3. Since you are using Dynamic SQL, you can use IF .. ELSE outside the query and construct the WHERE condition.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources