T-SQL | How to SET Value dynamically to Variables in While Loop
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
INSERT INTO VarTable (Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10, Var11, Var12) SELECT ,,,,,,,,,,, FROM (select MonthCol,COUNT(*) Cnt from #TempTable group by MonthCol) t PIVOT (SUM(Cnt) FOR MonthCol IN (,,,,,,,,,,,)) 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 -
MAX. I arbitrarily picked
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
- 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.