I'm trying to create a stored proc where the season and team name of a
sports team are passed to it, and it returns the week, opponent and
scoreline of the game, plus a path to the opponents logo.  The fields i
am working with in the table are season, week, hometeam, awayteam,
homescore and awayscore.  I'm kind of along the right lines, but I'm not
sure how to return the data that I want to the calling program :

create procedure nflc_fixture_list

(
    @season varchar(50) = '2010',
    @team varchar(50) = 'New England'

)

as

declare @opponent varchar(50)
declare @result varchar(50)
declare @image varchar(255)
declare @hometeam varchar(50)
declare @awayteam varchar(50)
declare @homescore varchar(50)
declare @awayscore varchar(50)
declare @week int


select @hometeam = hometeam, @awayteam = awayteam, @homescore =
homescore, @awayscore = awayscore,
@week = [week]
from nflc_fixtures
where (season = @season) and ((hometeam = @team) or (awayteam = @team))
order by week

if (@hometeam = @team)
begin
set @opponent = @awayteam
set @result = isnull(@homescore, '') + '-' + isnull(@awayscore, '')
set @image = '/images/small/' + replace(@opponent, ' ', '') + '.gif'
end


if (@awayteam = @team)
begin
set @opponent = @hometeam
set @result = isnull(@awayscore, '') + '-' + isnull(@homescore, '')
set @image = '/images/small/' + replace(@opponent, ' ', '') + '.gif'
end




GO


Any help would be really appreciated!


Cheers,

Mike




*** Sent via Developersdex http://www.developersdex.com ***

COudl you please post some DDL for your tables, that´´ll help finding
the right setbased solution rather than putting the values into
variables.

HTH, Jens Suessmeyer.

Hi Jens,

Here is the table design :

FixtureID    int    no    4    10       0        no    (n/a)    (n/a)    NULL
Season    varchar    no    50                      no    no    no    SQL_Latin1_General_CP1_CI_AS
Week    int    no    4    10       0        no    (n/a)    (n/a)    NULL
AwayTeam    varchar    no    50                      no    no    no    SQL_Latin1_General_CP1_CI_AS
HomeTeam    varchar    no    50                      no    no    no    SQL_Latin1_General_CP1_CI_AS
AwayScore    varchar    no    50            
    yes    no    no    SQL_Latin1_General_CP1_CI_AS
HomeScore    varchar    no    50            
    yes    no    no    SQL_Latin1_General_CP1_CI_AS


Cheers,

Mike



*** Sent via Developersdex http://www.developersdex.com ***

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.  It is very hard to debug code when you do not let us
see it.

The personal pseudo-code you posted seems to imply that you have a team
with a CHAR(50) name, and that magically most of your universe is that
same size!  If I did not know better, I would think that you made this
up on the fly!  Here is a guess, with a few of the names cleaned up

CREATE PROCEDURE Get_NFL_Fixtures
(@my_season INTEGER  = 2010,
@my_team CHAR(15) = 'New England')
AS
SELECT home_team, away_team, home_score, away_score, game_week,
   CASE WHEN @my_team = home_team THEN 'home' ELSE 'away' END
AS game_type
FROM nflc_fixtures
WHERE season = @season
  AND @team_name IN (home_team, away_team);
ORDER BY game_type;

This gives you a column for the sort.

Select where result are by the best match
where current of cursorName
Really really slow cursor
Problem copying data from column in same table
reverse like?