Can't compile stored procedure

Question

Post reply

Can't compile stored procedure

rconway-574047

Mr or Mrs. 500

Points: 522
More actions
October 11, 2010 at 1:01 pm

#143640

I am using SQL 2005 and I have created a stored procedure that creates a temp table with the data that I need in it. The temp table is comprised of three fields, AvgofFactor decimal(4,1), CorpNum INT, DStmtNum INT. The data for AvgofFactor are scores from a survey: 89.6, 99.7, 92.3, etc.. The for each score you have what corporation and default statement numbers those scores are associated to.
The second part of the stored procedure is where I am trying to get the percentile ranking for a score from a particular CorpNum and DStmtNum. What I am trying to do is create another temptable that would hold the percentile ranking for each DStmtNum and serve that back to a Crystal Report. Here is the second part of my sp:
[/code="sql"]CREATE TABLE #FinalPercentile
(
[Percentile] [int] NOT NULL,
[DStmtNum] [int] NOT NULL
)
DECLARE @Score decimal(4,2),
@DSN INT,
@rows INT
SET @Score = 0.0
DECLARE cDStmtNums CURSOR
FOR SELECT DISTINCT DStmtNum
FROM #RankTable
OPEN cDStmtNums
FETCH cDStmtNums INTO @DSN
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Score = AvgOfFactor
FROM #RankTable
WHERE (DStmtNum = @DSN) AND (CorpNum = @Corp5)
SET @rows = (SELECT COUNT(*) FROM #RankTable WHERE (DStmtNum = @DSN))
If @rows > 11
BEGIN
DECLARE @Per as INT, @RN as INT
With ScoreRank (RankNum, RowNumber, avgOfFactor, CorpNum) as
(
SELECT RANK() over (order by AvgofFActor) as RankNum,
Row_Number() over (order by AvgofFactor) as RowNumber,
AvgofFactor, corpNum
FROM #RankTable
WHERE (DStmtNum = @DSN)
)
SELECT @RN = (SELECT RankNum FROM ScoreRank WHERE (CorpNum = @Corp5))
SET @Per = ABS((1.0 * (1-@RN)/(@rows -1)*100)
END
Else
SET @Per = 0
INSERT #FinalPercentile VALUES(@Per, @DSN)
FETCH cDStmtNums INTO @DSN
END
CLOSE cDStmtNums
DEALLOCATE cDStmtNums
SELECT *
FROM #FinalPercentile[/code]
I am getting an 'Incorrect syntax near the keyword "END"' error message.
It is the END fot the BEGIN immediately following the 'If @rows > 11' line of code.
Thanks for any help!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you are missing one closing parenthesis just before that END that raises the error.:

SET @Per = ABS((1.0 * (1-@RN)/(@rows -1)*100)) --<--missing last closing parenthesis...you

also, in the code you posted, one variable @Corp5 was missing the declaration; i'm sure it's in your code snippet though.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rconway-574047 Mr or Mrs. 500 Points: 522 More actions · Answer 2

OMG!!!! Luv it when you look at code for over 30 minutes and miss something so small. The @Corp5 was declared in the first part of the sp which I did not post.

Thanks so much for the help!!!!