SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Whats wrong with the IF statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

lior3790
Starting Member

46 Posts

Posted - 05/27/2007 :  08:54:07  Show Profile  Reply with Quote
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)

Singapore
17650 Posts

Posted - 05/27/2007 :  09:05:28  Show Profile  Reply with Quote
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 - 05/27/2007 :  09:11:04  Show Profile  Reply with Quote
the simplicity,

Thanks KH
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 05/28/2007 :  00:49:36  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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)

Singapore
17650 Posts

Posted - 05/28/2007 :  00:51:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 05/28/2007 :  02:40:56  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000