Robin_Hcp Robin_Hcp -4 years ago 162
SQL Question

How to update values in a current table with a return value from a dynamic query?

I am trying to update the results of my query into an existing table, as you can see it's a dynamic query and I don't know how to insert the update function in this code. When I insert it into the while function it sendS QUERY EXECUTED SUCCESSFULLY but there isn't any update..

I used this code for the dynamic query:

Declare @SQL AS VARCHAR(MAX)
DECLARE @TABLENAME AS VARCHAR(max);
DECLARE @COLUMN1 AS VARCHAR(max);
DECLARE @COUNTER INT;
SET @TABLENAME = 'MOMENTUM_Results';
SET @COUNTER = 2;

WHILE @COUNTER<=122
BEGIN

SELECT @COLUMN1 = C.Name
FROM sys.Columns C
WHERE OBJECT_NAME(C.object_id) = @TABLENAME
AND C.column_id = @COUNTER
;



SET @SQL = 'SELECT AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ
Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 5'



EXECUTE (@SQL)



SET @COUNTER = @COUNTER + 1;

END


To obtain these results:

enter image description here

And now I want to update a current table by using this code:

UPDATE MOMENTUM_Results

SET @COLUMN1 = @SQL

WHERE [MNEMONIC]='RANK_5';


The problem is I don't know where to place it in the 1st code.. or how to properly use it.. and finally here is the final format of the existing table (final result I want to obtain, in which I want to insert the query results in the row RANK_5)

enter image description here

Does someone have any clue?

PRINT RESULT:

DECLARE @avg as float;

SELECT @avg = AVG(MR.[2006-12-30]) From MOMENTUM_Quintile MQ

Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[2006-12-30] = 5;

UPDATE MOMENTUM_Results_new SET @COLUMN1 = @avg
WHERE [MNEMONIC]=RANK_5


EXECUTE RESULT:

Msg 137, Level 15, State 1, Line 6
Must declare the scalar variable "@COLUMN1".
Msg 137, Level 15, State 1, Line 6
Must declare the scalar variable "@COLUMN1".
Msg 137, Level 15, State 1, Line 6
Must declare the scalar variable "@COLUMN1".
Msg 137, Level 15, State 1, Line 6
Must declare the scalar variable "@COLUMN1".
Msg 137, Level 15, State 1, Line 6
Must declare the scalar variable "@COLUMN1".

Answer Source

@SQL is just a string, not the SELECT result of your dynamic query, so executing SET @COLUMN1 = @SQL is not probably what you want. Also when you execute dynamic sql you should consider the dynamic query and the rest of your code as two "parallel universes", the only thing that connects them is the common tables they may be editing. I would advise you declare a variable inside the dynamic query, assign to it the result of your AVG function and execute UPDATE in the dynamic too. Something like this:

                Declare @SQL AS VARCHAR(MAX)
                DECLARE @TABLENAME AS VARCHAR(max);
                DECLARE @COLUMN1 AS VARCHAR(max);
                DECLARE @COUNTER INT;
                SET @TABLENAME = 'MOMENTUM_Results';
                SET @COUNTER = 2;

                WHILE @COUNTER<=122
                BEGIN

                    SELECT @COLUMN1 = C.Name
                    FROM sys.Columns C
                     WHERE OBJECT_NAME(C.object_id) = @TABLENAME 
                     AND C.column_id = @COUNTER
                      ;

                SET @SQL = 'DECLARE @avg float; 
                        SELECT @avg = AVG(MR.[' + @COLUMN1 + ']) From MOMENTUM_Quintile MQ 
                         Left Join MOMENTUM_Returns MR on MQ.Mnemonic = MR.Mnemonic WHERE MQ.[' + @COLUMN1 + '] = 5;
                        UPDATE '+@TABLENAME+' SET ' + @COLUMN1 + '= @avg
                        WHERE [MNEMONIC]=''RANK_5'''

               EXECUTE (@SQL)



                      SET @COUNTER = @COUNTER + 1;

                END

I may have syntax errors, I haven't tested it but you see my point

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download