T-SQL | How to SET Value dynamically to Variables in While Loop

dynamic sql assign value to variable
dynamic sql with variables
assign value to variable in sql select statement
assign exec result to variable sql server
sql set multiple variables from select
how to pass string variable in dynamic sql query
using variables in sql select statement
sql set variable from select

I have 12 Variables and all of them have the same SELECT Statement exept for 1 Param. I somehow cant figure out how to so it dynamically.

Here is an example of what I have:

// this here should be dynamic
SET @Var1 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '1')
SET @Var2 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '2')
SET @Var3 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '3')
SET @Var4 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '4')
SET @Var5 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '5')
SET @Var6 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '6')
SET @Var7 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '7')
SET @Var9 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '8')
SET @Var9 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '9')
SET @Var10 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '10')
SET @Var11 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '11')
SET @Var12 = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = '12')    

INSERT INTO VarTable (Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, Var11, Var12)
    VALUES (@Var1, @Var2, @Var3, @Var4, @Var5, @Var6, @Var7, @Var8, @Var9, @Var10, @Var11, @Var12)

The Table Cols are restricted (no permission to change the table) so I need to assign it like this.

This looks like it should be a PIVOT:

INSERT INTO VarTable (Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, Var11, Var12)
SELECT [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM (select MonthCol,COUNT(*) Cnt from #TempTable group by MonthCol) t
PIVOT (SUM(Cnt) FOR MonthCol IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) u

I believe we have to "pre-aggregate" the data in a subquery because COUNT(*) isn't allowed in the PIVOT clause1. Note that each MonthCol will have a single Cnt value so we could use any aggregate in the PIVOT that returns a single input unaltered - SUM, MIN or MAX. I arbitrarily picked SUM above.


1You have to supply specific column(s) to the aggregate.

dynamic sql, How do you assign a value to a variable in dynamic SQL? The WHILE loop executes the code between the begin and end once for each row in the temp table, starting with @currentrow set to 1. WHILE @currentrow <= @totalrows The next two lines select the first instance out of the temp table, write it to variable @servername and increment the @currentrow counter.

I think you don't need to use variable here :

INSERT INTO VarTable (Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, Var11, Var12)
      SELECT SUM(CASE WHEN MonthCol = 1 THEN 1 ELSE 0 END), 
             SUM(CASE WHEN MonthCol = 2 THEN 1 ELSE 0 END),
              . . .
             SUM(CASE WHEN MonthCol = 12 THEN 1 ELSE 0 END)
      FROM #TempTable t;

However, here conditional aggregation is enough as there is only 12 months. So, dynamic approach is not needed.

Creating Variables Dynamically (R, Python) - 미완성의 신, me! FROM ' + QUOTENAME(@tablename) + ' WHERE LastModified < DATEADD(DAY,' + @NumberOfKeepDays + ', GETDATE()) ORDER BY 1 DESC'; EXECUTE sys. In this article, we learned the SQL WHILE loop with quite simple examples. We also virtualized and explained the examples with flowcharts. WHILE loop helps us to achieve iterative operations in SQL Server. At the same time, BREAK and CONTINUE statements can be used to control iteration of the WHILE loop in SQL Server.

well this is what you can try here:

Declare a table variable with 2 columns

 declare @mytable table(
   VariableName varchar(255),
   VariableValue int
 )

Now Declare 2 more variables, one to set value and second to iterate through loop

 Declare @value int;
 Declare @month int = 1;

Now do the while loop like this:

 while (@month <= 12 ) --since there are only 12 months
 begin
   set @value = (SELECT COUNT(*) FROM #TempTable WHERE MonthCol = cast(@month as varchar));
  insert into  @mytable values ('Month'+cast (@month as varchar), value );
  set @month = @month +1;
 end

Now you'll have your DataSet in table form which you can access by using the simple query:

VariableName   VariableValue   
-----------    -------------- 
   Month1          23
   Month2          19
   Month3          66
   Month4          12
   Month5          05
   Month6          04
   Month7          63    
   Month8          76    
   Month9          21    
   Month10         23    
   Month11         63    
   Month12         12  



Select * from @mytable where VariableName = 'Month1' 

Variables in SQL procedures (DECLARE, SET statements), were assigned to var_1 ~ var_10 from 1 to 10 using the for statement. I use SET to assign values to a few of the variables. In this case, I want the @maxrecord variable to know how many records in total we must loop through. The @currentrecord acts as our counter. Once @currentrecord is equal the @maxrecord the loop will end.

Using Variables in Dynamic SQL, The SET statement can only be used on variable at a time. Although it's not quite cognitive, it can be used to handle a lot of tasks that SQL Server gives us a couple of options to execute character strings as T-SQL Code. I'm having a global Variable, which get increment in the Nested While Loop. In Each Iteration it Resets the Value to 1 (i.e., Initially Initialized Value). Sample Re-produce able Code: SET NOCOUN

Set in dynamic query value of variable declared outside dynamic , The thing is that i need to declare the variable outside the dynamic query. thanks for help ! EDIT : WHILE LOOP UNTIL SELECT COUNT xxx =  Obviously SELECT is more efficient than SET while assigning values to multiple variables in terms of statements executed, code and network bytes. What if variable is not populated successfully. If a variable is not successfully populated then behavior for SET and SELECT would be different.

SET @local_variable (Transact-SQL), Sets the specified local variable, previously created by using the DECLARE SCROLL ] [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY Specifies that the SET statement contains a declaration of a cursor. FETCH NEXT FROM @CursorVar; WHILE @@FETCH_STATUS = 0 BEGIN  This is actually why I want to use a table variable instead of a cursor. I generally look for a way to get my intended result using a JOIN on a table variable, but if I can't find a way to use a JOIN, then I can fall back on a loop on that same table variable. But I agree, set-based is best. – Kuyenda Oct 16 '09 at 14:16

Comments
  • Damn. Well this is what I needed. The other solutions mentioned are working too. But this is what I really asked for. Thanks. EDIT: Thanks for explaining how it works.