Scoring an assessment within a stored procedure with different external Parameters

Question

Context:

I am attempting to create a place for users to enter answers to a survey from clients. Scoring is different based on "XX Month" parameters. So I am bringing in the parameter and having a stored procedure calculate the score so they don't have to do it by paper.

Question:

What I came up with feel way bigger and messier than I feel like it can be (See Example 1). I tried putting a CASE WHEN statement (see Example 2 right after the declared variables, but SQL didn't like that.

I am open to any suggestions or help. I realize what I am asking, so if there is another place you know of to read up on how to do this better, I am happy doing some research (I haven't been able to find anything myself). Thank you for any help you can give.

Example 1

CREATE PROCEDURE [dbo].[CCW_USER_HH_ASQSE2]
(
    [--In order to shorten this SQL statement just know this is where I put my external Parameters. For example :@QuestionnaireVersion VarChar(10)])
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
         @Question1Score INT,
         @Question2Score INT,
         @Question3Score INT,
         @Question4Score INT,
         @Question5Score INT,
         @Question6Score INT,
         @Question7Score INT,
         @Question8Score INT

    SET @Question1Score = (CASE
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question1 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question1 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question1 = 'Rarely or Never' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question1 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question1 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question1 = 'Rarely or Never' THEN 10
                        End)
    SET @Question2Score = (CASE
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question2 = 'Often or Always' THEN 10
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question2 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question2 = 'Rarely or Never' THEN 0
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question2 = 'Often or Always' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question2 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question2 = 'Rarely or Never' THEN 0
                        End)
    SET @Question3Score = (CASE
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question3 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question3 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question3 = 'Rarely or Never' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question3 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question3 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question3 = 'Rarely or Never' THEN 10
                        End)
    SET @Question4Score = (CASE
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question4 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question4 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question4 = 'Rarely or Never' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question4 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question4 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question4 = 'Rarely or Never' THEN 10
                        End)
    SET @Question5Score = (CASE
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question5 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question5 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question5 = 'Rarely or Never' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question5 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question5 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question5 = 'Rarely or Never' THEN 10
                        End)
    SET @Question6Score = (CASE
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question6 = 'Often or Always' THEN 10
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question6 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question6 = 'Rarely or Never' THEN 0
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question6 = 'Often or Always' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question6 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question6 = 'Rarely or Never' THEN 0
                        End)
    SET @Question7Score = (CASE
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question7 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question7 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question7 = 'Rarely or Never' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question7 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question7 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question7 = 'Rarely or Never' THEN 10
                        End)
    SET @Question8Score = (CASE
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question8 = 'Often or Always' THEN 0
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question8 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '60 Month' AND @Question8 = 'Rarely or Never' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question8 = 'Often or Always' THEN 10
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question8 = 'Sometimes' THEN 5
                            WHEN @QuestionnaireVersion = '48 Month' AND @Question8 = 'Rarely or Never' THEN 0
                        End)

Example 2

...
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE
        @Question1Score INT,
        @Question2Score INT,
        @Question3Score INT,
        @Question4Score INT,
        @Question5Score INT,
        @Question6Score INT,
        @Question7Score INT,
        @Question8Score INT

CASE
   WHEN @QuestionnaireVersion = '60 Month' THEN SET @Question1Score =...
   WHEN @QuestionnaireVersion = '48 Month' THEN SET @Question1Score =...
...

Answer 1

Here is what I have come up with so far.

I created two "lookup tables" (LT__ASQSE2_ZVX and LT__ASQSE2_XVZ) and used some IF ELSE statements.

Also, there are 36 questions and 9 different "Month" surveys. So I have shortened it for readability.

USE [DEMO]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CCW_USER_HH_ASQSE2]
(
@QuestionnaireVersion VarChar(10),
@DocID int,
@Question1 VarChar(20),
@Question2 VarChar(20),
@Question3 VarChar(20)
)
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE
    @Question1Score INT,
    @Question2Score INT,
    @Question3Score INT

    IF @QuestionnaireVersion = '60 Month'
        BEGIN
            SET @Question1Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_ZVX
                                    WHERE QuestionAnswer = @Question1)
            SET @Question2Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_XVZ
                                    WHERE QuestionAnswer = @Question2)
            SET @Question3Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_XVZ
                                    WHERE QuestionAnswer = @Question3)
        END
    ELSE IF @QuestionnaireVersion = '48 Month'
        BEGIN
            SET @Question1Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_XVZ
                                    WHERE QuestionAnswer = @Question1)
            SET @Question2Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_ZVX
                                    WHERE QuestionAnswer = @Question2)
            SET @Question3Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_XVZ
                                    WHERE QuestionAnswer = @Question3)
        END
    ELSE IF @QuestionnaireVersion = '36 Month'
        BEGIN
            SET @Question1Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_XVZ
                                    WHERE QuestionAnswer = @Question1)
            SET @Question2Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_ZVX
                                    WHERE QuestionAnswer = @Question2)
            SET @Question3Score = (SELECT SCORE
                                    FROM LTC__ASQSE2_XVZ
                                    WHERE QuestionAnswer = @Question3)
        END
END

SELECT @Question1Score AS Question1Score, @Question2Score AS Question2Score, @Question3Score AS Question3Score