September 30, 2008 at 7:21 am
I have the following stored procedure that works fine in SS2005 but not in SS2000. SS2005 is smart enough to know I want the identity column generated, but SS2000 expects it to be part of the insert statement (i.e., it thinks I'm trying to insert mr.MBR_RACE_ID into the #Ranking.RANK column).
I need to update the MbrRace.MY_RANK with the number generated from the order of the temp table. A way to update the MbrRace table without using a temp table would also be fine. Suggestions?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[PostRanking]
(@RaceId INT)
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Ranking(
[RANK] [int] IDENTITY(1,1) NOT NULL,
[MBR_RACE_ID] [int] NOT NULL,
[TEAM_PTS] [int] NOT NULL,
[ALT_TEAM_PTS] [int] NOT NULL,
CONSTRAINT [PK_Ranking] PRIMARY KEY CLUSTERED
(
[RANK] ASC
)
) ON [PRIMARY]
insert into #Ranking
select mr.mbr_race_id, mr.team_pts, mr.alt_team_pts
from mbrrace mr where mr.race_id=@RaceId
order by (1000*mr.team_pts + mr.alt_team_pts) desc
update MbrRace set MY_RANK=(select r.RANK from #Ranking r WHERE r.MBR_RACE_ID=MbrRace.MBR_RACE_ID)
WHERE MbrRace.RACE_ID=@raceId
END
September 30, 2008 at 7:52 am
As far as it trying to insert into the identity column, you can get around that by just explicity defining the fields you are inserting into:
insert into #Ranking (MBR_Race_ID, Team_Points, Alt_Team_Points)
select mr.mbr_race_id, mr.team_pts, mr.alt_team_pts
from mbrrace mr where mr.race_id=@RaceId
order by (1000*mr.team_pts + mr.alt_team_pts) desc
September 30, 2008 at 9:00 pm
Doh - shoulda known that. Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply