How to use For each loop in SQL server

Question

I want to loop through a result set in SQL. So far I only know how to do this in powershell. See below:

foreach ($TestName in $DSFailures | % {$_.TestName}) {
    $Query=  "USE TestDS
              insert into #temptable 
              SELECT space(iteration * 4) + TheFullEntityName + ' (' + rtrim(TheType) + ')' as EntityName, *
              FROM    dbo.fn_DependantObjects('$TestName',    1,     0)    
              ORDER BY ThePath" 
    Invoke-Sqlcmd -ServerInstance "SQL2016" -Database "db" -Query $Query

How can I achieve this in SQL?

Possible duplicate of [SQL Server FOR EACH Loop](https://stackoverflow.com/questions/10300414/sql-server-for-each-loop) — Oscar, Jan 04 '18 at 11:22

score 1 · Accepted Answer · answered Jan 08 '18 at 09:36

Here is the answer:

Function Get-FunctionDependencies{ 
 $FPM_Functions =  "select replace (name, '_CLR','') AS FPM_FunctionName
                    into #temptable1
                    from sys.objects
                    where name like 'fn_clr%'
                    select * from #temptable1"
  $GetCLRCallingFunctions = Invoke-Sqlcmd -ServerInstance "sql" -Database "DB" -Query  $FPM_Functions
  foreach ($FPM_FunctionName in $GetCLRCallingFunctions | % {$_.FPM_FunctionName}) {
  Write-Output "--These are the dependencies for $FPM_FunctionName" 
  $query1 = "  SELECT referencing_entity_name as [FPM Function Dependencies] FROM sys.dm_sql_referencing_entities ('dbo.$FPM_FunctionName', 'OBJECT');"

How to use For each loop in SQL server

1 Answers1