x

Dynamic SQL/ EXECUTE sp_executesql

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

more ▼

asked Apr 21, 2010 at 11:00 AM in Default

OnlyGraphite gravatar image

OnlyGraphite
41 6 6 7

This is a formatting job for superman ... AKA @FatherJack!
Apr 21, 2010 at 11:04 AM sp_lock
Swoosh. Someone called? Gnrgggh, fmmmmck, bbrrrnnt. 'Pop'
Apr 21, 2010 at 11:24 AM Fatherjack ♦♦
@Fatherjack you working on sainthood? Nice work
Apr 21, 2010 at 11:49 AM Grant Fritchey ♦♦
@Fatherjack, also, saw your tweet in the 2010 Red Gate book. Congrats.
Apr 21, 2010 at 11:49 AM Grant Fritchey ♦♦
@Grant, I actually find it quite cathartic to see the before and after!! yeah, I didnt know it was in there until RedGate sent me a copy, must admin I had a smile on my face :D
Apr 21, 2010 at 12:09 PM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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...

more ▼

answered Apr 21, 2010 at 07:01 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Apr 21, 2010 at 03:00 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Apr 21, 2010 at 06:15 PM

OnlyGraphite gravatar image

OnlyGraphite
41 6 6 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x986
x51

asked: Apr 21, 2010 at 11:00 AM

Seen: 2233 times

Last Updated: Apr 21, 2010 at 11:32 AM