# Dynamic SQL/ EXECUTE sp_executesql

 0 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 41 ● 6 ● 6 ● 7 Fatherjack ♦♦ 42.8k ● 75 ● 79 ● 108 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 ♦♦ add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 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... more ▼ answered Apr 21, 2010 at 07:01 PM Matt Whitfield ♦♦ 29.5k ● 61 ● 65 ● 87 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 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. more ▼ answered Apr 21, 2010 at 03:00 PM TimothyAWiseman 15.6k ● 20 ● 23 ● 32 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users
 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 more ▼ answered Apr 21, 2010 at 06:15 PM OnlyGraphite 41 ● 6 ● 6 ● 7 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

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

By Email:

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

Topics:

x990
x51

asked: Apr 21, 2010 at 11:00 AM

Seen: 2269 times

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