Dynamic SQL/ EXECUTE sp_executesql

Question

Dear All,

I need your help in completing this Proc.

I need to calculate the mean score for these values… below is the formulae to calculate the mean.

Mean = ((@AgreeStrongly * 5) + (@Agree * 4) + (@Neither * 3) + (@Disagree * 2) + (@DisagreeStrongly * 1))/((@AgreeStrongly) + (@Agree) + (@Neither) + (@Disagree) + (@DisagreeStrongly))

Example:

((10*5)+(8*4)+(7*3)+(3*2)+(2*1))/SUM(10 + 8 + 7 + 3 + 2) = 3.70

Table Structure:  
Question Table  
Q.No  Data_Col_No   Question                        Project  
Q1  1              What is your name?               ABC  
Q2  3              What is your favourite color?    ABC  
Q3  NULL           Open Question…                   ABC

Answer Table  
Project D1  D2  D3  D4  D5  
ABC     2   1   3   1   5  
ABC     1   2   1   1   4  
ABC     3   2   1   2   1

In the above 2 tables Question.Data_Col.No = Answer.‘D’ + (Question.Data_Col.No)

I.e. if we refer to Q2 in QuestionTable then it should look for D3 column in the AnswerTable.

I had made some progress in capturing the count numbers for one element to calculate mean score… below is the proc. However I unable to proceed further in terms of extending the Proc to calculate all the elements of mean score.

ALTER Procedure [dbo].[MyProc]
    @Q_No NVarchar(5),
    @Survey NVarchar(500)
AS 
    Declare @SQLString nvarchar(max)
    Declare @SQLString2 Nvarchar(Max)
    Declare @id int
    Declare @id2 int
    Declare @AgreeStrongly int
    Declare @Agree int

    SET @id = 1
    SET @SQLString = 'SELECT Count (case '

    WHILE @id < = 150
        BEGIN
            SET @sqlString = @sqlString + ' When Data_col_no = '
                + convert(varchar, @id) + ' then (D' + convert(varchar, @id)
                + ')'
            Set @id = @id + 1
        END

    SET @sqlstring = @sqlstring
        + ' END) as answer FROM Question,Answer where Question.Q_no = '''
        + @Q_No + '''' + ' and Project = ' + '''' + @Survey + '''' + ' and '
    SET @id2 = 1
    SET @SQLString2 = '(case '

    While @id2 < = 150
        Begin
            Set @SQLString2 = @SQLString2 + 'When Data_Col_No =  '
                + Convert(varchar, @id2) + ' then (D' + Convert(Varchar, @id2)
                + ') '
            Set @id2 = @id2 + 1
        End
    SET @SQLString = @SQLString + @SQLString2 + ' End) = 1'


    EXECUTE sp_executesql @SQLString

Your help is much appreciated…

Thanks in advance.

Cheers,

D

Answer 1

0

Here is a small snippet that demonstrates how to output to a variable using sp_executesql

DECLARE @outputVariable [int]
EXEC sp_executesql N'SELECT @outputVariable = (SELECT TOP 1 object_id from sys.objects)', N'@outputVariable int OUTPUT', @outputVariable OUTPUT
SELECT @outputVariable

That should give you the general idea...

answered Apr 21 '10 at 07:01 PM

Matt Whitfield ♦♦
29.2k ● 56 ● 63 ● 87

(comments are locked)

Answer 2

0

One option might be to write a UDF which adjusts the values appropriately (taking the Strongly Agree and returning 5 for instance) and then simply use the avg function over the udf on that column. That should give you what you want without using Dynamic SQL.

As a general rule, Dynamic SQL is a wonderful thing that lets you do things you couldn't otherwise, but it should be used sparingly only when it is required.

answered Apr 21 '10 at 03:00 PM

TimothyAWiseman
14.4k ● 17 ● 21 ● 29

(comments are locked)

Answer 3

0

Hi Timothy,

Thank you for quick response. Initially I wanted to use UDF however I had shifted to SP because I need to use Dynamic SQL. Just to give you some background... MEAN score is calculated on any column (not specific column) and for any project. So my column name is not constant... hence I had to use dynamic SQL.

I am getting the result but I am not sure on how to save these values into variable to calculate the mean score.

Any help is much appreciated.

Thanks in anticipation.

Cheers, D

answered Apr 21 '10 at 06:15 PM

OnlyGraphite
41 ● 6 ● 6 ● 7

(comments are locked)

Dynamic SQL/ EXECUTE sp_executesql

Follow this question

Related Questions