Function that returns rowset, to feed a loop cycling through rows

• Marios Philippopoulos

  SSC Guru

  Points: 57190

  More actions

  January 28, 2009 at 1:07 pm

  #204730

  Hi,

  I want to write a function that returns a rowset and in turn feeds that to a loop.

  Here is the pseudocode:

  function getInstances()

  {

  $instance1 = "srvr\inst"

  sqlcmd `-S $instance1 `-i getInstances.sql

  }

  $rowSet = getInstances

  # $rowSet contains list of SQL instances

  $i=0

  while ($i < length of $rowSet)

  {

  sqlcmd `-S $rowSet[$i] `-i getInstanceInfo.sql

  $i++

  }

  In short, the script gets the list of SQL instances into an array structure and cycles through that array in a loop executing a sqlcmd command in each iteration.

  The problem I'm having is, I don't know how to store the output of the function to a rowset/array and feed that to a loop.

  Anyone have any sample code like this to share?

  Much appreciated.

  __________________________________________________________________________________
  SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
  Persisting SQL Server Index-Usage Statistics with MERGE[/url]
  Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

• matt stockham

  SSCrazy Eights

  Points: 9942

  More actions

  January 28, 2009 at 2:52 pm

  #934239

  I added the -h parameter to sqlcmd to drop headers. You also need to set nocount or parse out the returned values to only return valid instances.

  function getInstances()

  {

  $instance1 = "srvr\inst"

  sqlcmd -S $instance1 -i getInstances.sql -h -1

  }

  foreach ($inst in getInstances) {

  sqlcmd -S $inst -i getInstanceInfo.sql

  }

• Marios Philippopoulos

  SSC Guru

  Points: 57190

  More actions

  January 29, 2009 at 3:40 am

  #934492

  matt stockham (1/28/2009)

  ---

  I added the -h parameter to sqlcmd to drop headers. You also need to set nocount or parse out the returned values to only return valid instances.

  function getInstances()

  {

  $instance1 = "srvr\inst"

  sqlcmd -S $instance1 -i getInstances.sql -h -1

  }

  foreach ($inst in getInstances) {

  sqlcmd -S $inst -i getInstanceInfo.sql

  }

  Thank you! I will give that a try.

  __________________________________________________________________________________
  SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
  Persisting SQL Server Index-Usage Statistics with MERGE[/url]
  Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]