July 7, 2004 at 3:16 pm
Hello,
I have a procedure called MM_G1_SFMath_A_EOY_AUTO_sp that takes two parameters. The procedure performs an Insert/Select into a table, and then calls another procedure called ADPopluateMMScores_sp, which takes three parameters.
When I execute MM_G1_SFMath_A_EOY_AUTO_sp, the insert works fine, but ADPopluateMMScores_sp does not execute because of a problem I think I am having with a parameter it needs called ‘@TestGrade’ (smallint). I have attempted to assign a field from the ‘Select’ list (called ‘TestGrade’, smallint) that the ‘Insert’ uses, to the @TestGrade parameter, and this seems to be where the problem occurs.
When I execute ADPopluateMMScores_sp by itself and assign an integer to @TestGrade, and then assign values to the other two parameters, the procedure runs without any problems. When I configure ‘@TestGrade smallint = NULL’ in ADPopluateMMScores_sp, and try to run MM_G1_SFMath_A_EOY_AUTO_sp with @TestGrade commented out, ADPopluateMMScores_sp will also execute then.
The following is the code for MM_G1_SFMath_A_EOY_AUTO_sp:
Alter Procedure MM_G1_SFMath_A_EOY_AUTO_sp
@ProcessPermnum varchar(12),
@ProcessTestShortName1 nvarchar(8)
AS
Declare
@TSName1 as nvarchar(8)
Set @TSName1 = @ProcessTestShortName1
Insert tblMMStanTestScores
(Grade, TestShortName, Permnum, QID, QNum, NullChk, Score, CA, SA)
Select
TestGrade,
TestShortName = @TSName1,
Permnum,
QID,
QNum,
CASE
WHEN SA IS NULL THEN 'B'
WHEN SA = CA THEN 'C'
ELSE 'I'
END AS NullChk,
CASE WHEN SA=CA THEN 1 ELSE 0
END as Score,
CA,
SA
FROM
(
SELECT
AD.TestGrade,
AD.TestShortName,
AD.Permnum,
KY.QID,
KY.CA,
Case
When KY.QNumber = 1 Then AD.Q1
When KY.QNumber = 2 Then AD.Q2
When KY.QNumber = 3 Then AD.Q3
When KY.QNumber = 4 Then AD.Q4
When KY.QNumber = 5 Then AD.Q5
When KY.QNumber = 6 Then AD.Q6
When KY.QNumber = 7 Then AD.Q7
When KY.QNumber = 8 Then AD.Q8
When KY.QNumber = 9 Then AD.Q9
When KY.QNumber = 10 Then AD.Q10
When KY.QNumber = 11 Then AD.Q11
When KY.QNumber = 12 Then AD.Q12
When KY.QNumber = 13 Then AD.Q13
When KY.QNumber = 14 Then AD.Q14
When KY.QNumber = 15 Then AD.Q15
When KY.QNumber = 16 Then AD.Q16
When KY.QNumber = 17 Then AD.Q17
When KY.QNumber = 18 Then AD.Q18
When KY.QNumber = 19 Then AD.Q19
When KY.QNumber = 20 Then AD.Q20
When KY.QNumber = 21 Then AD.Q21
When KY.QNumber = 22 Then AD.Q22
When KY.QNumber = 23 Then AD.Q23
When KY.QNumber = 24 Then AD.Q24
When KY.QNumber = 25 Then AD.Q25
When KY.QNumber = 26 Then AD.Q26
When KY.QNumber = 27 Then AD.Q27
When KY.QNumber = 28 Then AD.Q28
When KY.QNumber = 29 Then AD.Q29
When KY.QNumber = 30 Then AD.Q30
When KY.QNumber = 31 Then AD.Q31
When KY.QNumber = 32 Then AD.Q32
When KY.QNumber = 33 Then AD.Q33
When KY.QNumber = 34 Then AD.Q34
When KY.QNumber = 35 Then AD.Q35
When KY.QNumber = 36 Then AD.Q36
When KY.QNumber = 37 Then AD.Q37
When KY.QNumber = 38 Then AD.Q38
When KY.QNumber = 39 Then AD.Q39
End AS SA,
'Q'+ str(KY.QNumber,2)as QNum
From tblADRawAnswerData AD Cross Join tblMM_G1_SFMath_A_EOY_KEY KY
Where KY.QNumber between 1 and 39
and
AD.Permnum = @ProcessPermnum
and
AD.TestShortName = @TSName1
and
AD.Completed = 1
)AS Inline
Order by TestGrade, TestShortName, Permnum, QNum, QID
EXEC ADPopluateMMScores_sp
@TestGrade = TestGrade,
@Permnum = @ProcessPermnum,
@TestShortName1 = @TSName1
Notice the ‘EXEC ADPopluateMMScores_sp’ at the end of the procedure. I need to somehow get the value of ‘TestGrade’ in the outer select list, and assign that to @TestGrade for the ADPopluateMMScores_sp procedure.
With the @TestGrade = TestGrade assignment, when I try to execute MM_G1_SFMath_A_EOY_AUTO_sp, ADPopluateMMScores_sp does not execute, and I get the following error:
---
Server: Msg 8114, Level 16, State 4, Procedure ADPopluateMMScores_sp, Line 0
Error converting data type nvarchar to smallint.
---
@TestGrade’s datatype is the same in both procedures, and they are consistent with the datatypes of the table fields.
Is there a way I can get the ‘TestGrade’ value from the select list, and assign it to the @TestGrade parameter that ADPopluateMMScores_sp is looking for?
Thank you for your help!
CSDunn
July 7, 2004 at 5:30 pm
Try this:
Alter Procedure MM_G1_SFMath_A_EOY_AUTO_sp
@ProcessPermnum varchar(12),
@ProcessTestShortName1 nvarchar(8)
AS
Declare
@TSName1 as nvarchar(8),
@TestGradeValue smallint
Set @TSName1 = @ProcessTestShortName1
Insert tblMMStanTestScores
(Grade, TestShortName, Permnum, QID, QNum, NullChk, Score, CA, SA)
Select
@TestGradeValue = TestGrade,
TestShortName = @TSName1,
Permnum,
QID,
QNum,
CASE
WHEN SA IS NULL THEN 'B'
WHEN SA = CA THEN 'C'
ELSE 'I'
END AS NullChk,
CASE WHEN SA=CA THEN 1 ELSE 0
END as Score,
CA,
SA
FROM
(
SELECT
AD.TestGrade,
AD.TestShortName,
AD.Permnum,
KY.QID,
KY.CA,
Case
When KY.QNumber = 1 Then AD.Q1
When KY.QNumber = 2 Then AD.Q2
When KY.QNumber = 3 Then AD.Q3
When KY.QNumber = 4 Then AD.Q4
When KY.QNumber = 5 Then AD.Q5
When KY.QNumber = 6 Then AD.Q6
When KY.QNumber = 7 Then AD.Q7
When KY.QNumber = 8 Then AD.Q8
When KY.QNumber = 9 Then AD.Q9
When KY.QNumber = 10 Then AD.Q10
When KY.QNumber = 11 Then AD.Q11
When KY.QNumber = 12 Then AD.Q12
When KY.QNumber = 13 Then AD.Q13
When KY.QNumber = 14 Then AD.Q14
When KY.QNumber = 15 Then AD.Q15
When KY.QNumber = 16 Then AD.Q16
When KY.QNumber = 17 Then AD.Q17
When KY.QNumber = 18 Then AD.Q18
When KY.QNumber = 19 Then AD.Q19
When KY.QNumber = 20 Then AD.Q20
When KY.QNumber = 21 Then AD.Q21
When KY.QNumber = 22 Then AD.Q22
When KY.QNumber = 23 Then AD.Q23
When KY.QNumber = 24 Then AD.Q24
When KY.QNumber = 25 Then AD.Q25
When KY.QNumber = 26 Then AD.Q26
When KY.QNumber = 27 Then AD.Q27
When KY.QNumber = 28 Then AD.Q28
When KY.QNumber = 29 Then AD.Q29
When KY.QNumber = 30 Then AD.Q30
When KY.QNumber = 31 Then AD.Q31
When KY.QNumber = 32 Then AD.Q32
When KY.QNumber = 33 Then AD.Q33
When KY.QNumber = 34 Then AD.Q34
When KY.QNumber = 35 Then AD.Q35
When KY.QNumber = 36 Then AD.Q36
When KY.QNumber = 37 Then AD.Q37
When KY.QNumber = 38 Then AD.Q38
When KY.QNumber = 39 Then AD.Q39
End AS SA,
'Q'+ str(KY.QNumber,2)as QNum
From tblADRawAnswerData AD Cross Join tblMM_G1_SFMath_A_EOY_KEY KY
Where KY.QNumber between 1 and 39
and
AD.Permnum = @ProcessPermnum
and
AD.TestShortName = @TSName1
and
AD.Completed = 1
)AS Inline
Order by TestGrade, TestShortName, Permnum, QNum, QID
EXEC ADPopluateMMScores_sp
@TestGrade = @TestGradeValue,
@Permnum = @ProcessPermnum,
@TestShortName1 = @TSName1
July 7, 2004 at 5:46 pm
Thank you for your help, I will try this.
CSDunn
July 9, 2004 at 11:11 am
No luck. When I tried the variable assignment as you prescribed, I got the following error message when I attempted to 'Alter' the procedure:
Server: Msg 141, Level 15, State 1, Procedure MM_G1_SFMath_A_EOY_AUTO_sp, Line 95
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Is there anything else I can do to get the TestGrade value from 'TestGrade' in the select list, and assign the value to the '@TestGrade' parameter for the ADPopluateMMScores_sp procedure?
Thanks again!
CSDunn
July 9, 2004 at 11:20 am
You will probably have to run your Select query prior to performing the insert and do your variable assignment here.
For example:
Declare @TestGradeValue smallint
Select @TestGradeValue = TestGrade
FROM
(
SELECT
AD.TestGrade,
From tblADRawAnswerData AD
Cross Join tblMM_G1_SFMath_A_EOY_KEY KY
Where KY.QNumber between 1 and 39
and
AD.Permnum = @ProcessPermnum
and
AD.TestShortName = @TSName1
and
AD.Completed = 1
)AS Inline
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply