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.
Example:
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.
Your help is much appreciated… Thanks in advance. Cheers, D
(comments are locked)

Here is a small snippet that demonstrates how to output to a variable using sp_executesql
That should give you the general idea...
(comments are locked)

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.
(comments are locked)

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
(comments are locked)

This is a formatting job for superman ... AKA @FatherJack!
Swoosh. Someone called? Gnrgggh, fmmmmck, bbrrrnnt. 'Pop'
@Fatherjack you working on sainthood? Nice work
@Fatherjack, also, saw your tweet in the 2010 Red Gate book. Congrats.
@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