Create Stored Procedure - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Create Stored Procedure

Author

Topic

Trogs
Starting Member

2 Posts

Posted - 2014-05-31 : 19:18:41

Having trouble getting my head around this question.

Write a stored procedure called Winning_Coaches that will list the winning coaches for any given round.

GO
CREATE PROC Winning_Coaches @tnum int, @tname
AS
SELECT coach FROM teams, results
WHERE tnum = @tnum, tname = @tname
and homescore > awayscore or awayscore > homescore
GO
EXEC team_coach1 1

--- For example Exec Winning_Coaches 5 should return the names of coaches that won in round 5.

--- The procedure should return an error when a round number is not entered or when a non valid round number is entered.

Tables-------

create table teams (
tnum int not null,
tname varchar(30),
country char(2),
coach varchar(100),
points int,
captain_id int,
primary key (tnum)
)

create table results (
roundnumber int,
hometeam int not null,
awayteam int not null,
gamedate varchar(100),
homescore int,
awayscore int
primary key (roundnumber, hometeam, awayteam),
foreign key (hometeam) references teams,
foreign key (awayteam) references teams
)

-------------------

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-06-02 : 09:55:04

Please provide some sample data and the expected output from your proc when executed against the sample data.

btw there are a few errors in your create procedure:
1. no data type for @tname (you probably want varchar(30)2
2. the comma in the WHERE clause is invalid (should it be the word "AND" ?)
3. the last clause (about the scores) should probably be in parentheses (remember the implicit precedence of AND vs OR)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-06-02 : 16:57:54

[code]
CREATE PROCEDURE Winning_Coaches
@roundnumber int
AS
SET NOCOUNT ON
DECLARE @rowcount int

SELECT t.coach
FROM results r
INNER JOIN teams t ON
t.tnum = CASE WHEN r.homescore > r.awayscore THEN r.hometeam ELSE r.awayteam END
WHERE
r.roundnumber = @roundnumber
SET @rowcount = @@ROWCOUNT

IF @rowcount = 0
BEGIN
RAISERROR('ERROR: Round Number Entered, %i, was not found in the Results table.', 16, 1, @roundnumber)
RETURN -1
END
RETURN 0

[/code]

Trogs
Starting Member

2 Posts

Posted - 2014-06-02 : 18:28:10

@ScottPletcher you're a genius

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources