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)
 Whats wrong with the IF statement

Author  Topic 

lior3790
Starting Member

46 Posts

Posted - 2007-05-27 : 08:54:07
Hi,
I'm trying top retrieve information based on parameters sent to the procedure.
i need to go into an IF satement in order for me to check if i need to set the parameter value or to use the inputed one.
I get en error for the IF statement.

can someone please advice me whats wrong?

ALTER PROCEDURE [dbo].[LeagueTableProcedure] 
@league int=null,
@nation int=null ,
@season int=null Output,
@round int=null

AS
begin

if @league=null
SELECT @league=Competitions.CompetitionId, @season=Seasons.Seasonid, @round=Rounds.RoundId
FROM Competitions INNER JOIN
Seasons ON Competitions.CompetitionId = Seasons.CompetitionId INNER JOIN
Rounds ON Seasons.Seasonid = Rounds.SeasonId
WHERE (Competitions.AreaId = @nation) AND (Seasons.EndDate > getdate())
end if

IF (@season=null)
SELECT @season=Seasons.Seasonid, @round=Rounds.RoundId
FROM Competitions INNER JOIN
Seasons ON Competitions.CompetitionId = Seasons.CompetitionId INNER JOIN
Rounds ON Seasons.Seasonid = Rounds.SeasonId
WHERE (Competitions.AreaId = @nation) AND (Seasons.EndDate > GETDATE()) AND (Competitions.CompetitionId = @league) AND
(Rounds.RoundEnd > GETDATE())
end if

if @round=null then begin
SELECT @round=Rounds.RoundId
FROM Seasons INNER JOIN
Rounds ON Seasons.Seasonid = Rounds.SeasonId
WHERE (Rounds.RoundEnd > GETDATE())
end
end if



end


SELECT t.Team_name, t.TeamID, lt.rank, lt.MatchWon, lt.MatchDraw, lt.MatchLost, lt.points, lt.GoalsScored, lt.GoalsRecieved, la.rank AS awayRank,
la.MatchWon AS awayWon, la.MatchDraw AS awayDraw, la.MatchLost AS awayLost, la.points AS awayPoints, la.GoalsScored AS awayScored,
la.GoalsRecieved AS awayRecieved, lh.rank AS homeRank, lh.MatchWon AS homeWon, lh.MatchDraw AS homeDraw, lh.MatchLost AS homeLost,
lh.points AS homePoints, lh.GoalsScored AS homeScored, lh.GoalsRecieved AS homeRecieved,
(SELECT COUNT(TeamTableInfo.TeamID) AS Expr1
FROM Seasons INNER JOIN
Competitions ON Seasons.CompetitionId = Competitions.CompetitionId INNER JOIN
Rounds ON Seasons.Seasonid = Rounds.SeasonId INNER JOIN
TeamTableInfo INNER JOIN
LeagueTableTotal ON TeamTableInfo.TeamID = LeagueTableTotal.TeamId ON Rounds.RoundId = LeagueTableTotal.RoundId
WHERE (Seasons.CompetitionId = @league) AND (t.TeamID = TeamTableInfo.TeamID)
GROUP BY TeamTableInfo.TeamID) AS gamePlayed, t.TeamID AS Expr1
FROM Seasons AS s INNER JOIN
Competitions AS c ON s.CompetitionId = c.CompetitionId INNER JOIN
Rounds AS r ON s.Seasonid = r.SeasonId INNER JOIN
TeamTableInfo AS t INNER JOIN
LeagueTableTotal AS lt ON t.TeamID = lt.TeamId ON r.RoundId = lt.RoundId INNER JOIN
LeagueTableAway AS la ON r.RoundId = la.RoundId INNER JOIN
LeagueTableHome AS lh ON r.RoundId = lh.RoundId
WHERE (r.RoundId = @round)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-27 : 09:05:28
from Books OnLine
quote:

IF...ELSE
Imposes conditions on the execution of a Transact-SQL statement. The Transact-SQL statement following an IF keyword and its condition is executed if the condition is satisfied (when the Boolean expression returns TRUE). The optional ELSE keyword introduces an alternate Transact-SQL statement that is executed when the IF condition is not satisfied (when the Boolean expression returns FALSE).

Syntax
IF Boolean_expression
{ sql_statement | statement_block }
[ ELSE
{ sql_statement | statement_block } ]



There isn't a END IF for T-SQL. You can enclose the statement block in BEGIN .... END
example

IF @league=null
BEGIN
SELECT @league=Competitions.CompetitionId, @season=Seasons.Seasonid, @round=Rounds.RoundId
FROM Competitions INNER JOIN
Seasons ON Competitions.CompetitionId = Seasons.CompetitionId INNER JOIN
Rounds ON Seasons.Seasonid = Rounds.SeasonId
WHERE (Competitions.AreaId = @nation) AND (Seasons.EndDate > getdate())
END



KH

Go to Top of Page

lior3790
Starting Member

46 Posts

Posted - 2007-05-27 : 09:11:04
the simplicity,

Thanks KH
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-28 : 00:49:36
Also
If @league=null

should be

If @league is null




Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-28 : 00:51:09
quote:
Originally posted by madhivanan

Also
If @league=null

should be

If @league is null

Madhivanan

Failing to plan is Planning to fail


Good catch


KH

Go to Top of Page

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-05-28 : 02:40:56
also you might want to read this about different strategies for dynamic search conditions:

http://www.sommarskog.se/dyn-search.html


www.elsasoft.org
Go to Top of Page
   

- Advertisement -