if och else i stored procedure

andreas_lindhMedlem sedan jan. 20011 140 inlägg

Trådstart25 sep. 2005 19:37#1

Försöker använda if och else i en stored procedure

LTER PROCEDURE vgr_saveGames
@gameID int,
@divisionID int,
@round nvarchar(25),
@gameNumber bigInt,
@gameDate dateTime,
@gameTIme nvarchar(5),
@hometeam int,
@awayteam int,
@homescore int,
@awayscore int,
@gameEnded bit,
@overtime int
AS
if @gameID = 0
	if exists(select gameID from r_games where gameNumber = @gameNumber and divisionID = @divisionID)
		update r_Games set gameNumber = gameNumber + 1 
		where divisionID = @divisionID and gameNumber >= @gameNumber
		insert into r_Games(divisionID,[round],gameNumber,gameDate,gameTime,hometeam,awayteam)
		values(@divisionID,@round,@gameNumber,@gameDate,@gameTime,@hometeam,@awayteam)
	else
		insert into r_Games(divisionID,[round],gameNumber,gameDate,gameTime,hometeam,awayteam)
		values(@divisionID,@round,@gameNumber,@gameDate,@gameTime,@hometeam,@awayteam)
else
	update r_Games set gameDate = @gameDate,gameTime = @gameTIme,[round] = @round,gameNumber = @gameNumber,
	homeTeam = @homeTeam,awayTeam = @awayteam,homeScore = @homeScore,awayScore= @homeScore 
	where gameID = @gameID

Men jag får felmeddelandet
"Incorrect syntax near the keyword 'else'"

Kan jag inte göra så här eller är det något annat fel?

emissionMedlem sedan dec. 19996 721 inlägg

25 sep. 2005 19:45#2

Du måste kapsla in, annars förstår inte SQL-tolken vad du vill göra

if @gameID = 0
BEGIN
	if exists(select gameID from r_games where gameNumber = @gameNumber and divisionID = @divisionID)
        BEGIN
		update r_Games set gameNumber = gameNumber + 1 
		where divisionID = @divisionID and gameNumber >= @gameNumber
		insert into r_Games(divisionID,[round],gameNumber,gameDate,gameTime,hometeam,awayteam)
		values(@divisionID,@round,@gameNumber,@gameDate,@gameTime,@h  ometeam,@awayteam)
        END
	else
        BEGIN
		insert into r_Games(divisionID,[round],gameNumber,gameDate,gameTime,hometeam,awayteam)
		values(@divisionID,@round,@gameNumber,@gameDate,@gameTime,@h  ometeam,@awayteam)
        END
END
else
BEGIN
	update r_Games set gameDate = @gameDate,gameTime = @gameTIme,[round] = @round,gameNumber = @gameNumber,
	homeTeam = @homeTeam,awayTeam = @awayteam,homeScore = @homeScore,awayScore= @homeScore 
	where gameID = @gameID
END