Problem with Parameter Assignment

  • 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

     

  • 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

     

  • Thank you for your help, I will try this.

     

    CSDunn

  • 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 

  • 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