Call SP foreach Column in Table (without Cursor)

• Alan Burstein

  SSC Guru

  Points: 61136

  More actions

  August 20, 2013 at 3:55 pm

  #1643254

  opc.three (8/19/2013)

  ---

  Here is a non-cursor option, just for the sake of having an alternative to using a cursor:

  DECLARE @sql NVARCHAR(MAX) = N'';

  SELECT @sql += 'EXEC LoadFile ' + QUOTENAME(FILENAME, '''') + ';' FROM tbl;

  EXEC (@sql);

  For the reasons outlined in this thread I would recommend using this non-cursor method instead:

  DECLARE @sql nvarchar(max)

  SET @sql=(

  SELECT 'EXEC LoadFile '+QUOTENAME(FILENAME, '''')+';'

  FROM tbl

  FOR XML PATH(''))

  EXEC(@sql);

  "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

  -- Itzik Ben-Gan 2001

• Orlando Colamatteo

  SSC Guru

  Points: 182276

  More actions

  August 20, 2013 at 6:44 pm

  #1643272

  Alan.B (8/20/2013)

  ---

  opc.three (8/19/2013)

  ---

  Here is a non-cursor option, just for the sake of having an alternative to using a cursor:

  DECLARE @sql NVARCHAR(MAX) = N'';

  SELECT @sql += 'EXEC LoadFile ' + QUOTENAME(FILENAME, '''') + ';' FROM tbl;

  EXEC (@sql);

  For the reasons outlined in this thread I would recommend using this non-cursor method instead:

  DECLARE @sql nvarchar(max)

  SET @sql=(

  SELECT 'EXEC LoadFile '+QUOTENAME(FILENAME, '''')+';'

  FROM tbl

  FOR XML PATH(''))

  EXEC(@sql);

  I appreciate the intent behind your comment but there is nothing wrong with aggregate concatenation when used appropriately. Also, regarding the code you posted, you're missing an important point about how FOR XML behaves. If I were going to use the XML method I would use TYPE along with FOR XML to avoid entitization problems. Please see my last response on this thread for an example of what I mean.

  Since we're throwing out alternative solutions, in addition to the aggregate concatenation technique I also favor this SQLCLR aggregate I wrote over using a cursor or FOR XML: http://groupconcat.codeplex.com

  Here would be an example of its use you could add to the end of the testbed code I provided in my last post on this thread:

  SET @sql = (

  SELECT dbo.GROUP_CONCAT_DS('EXEC LoadFile ' + QUOTENAME(MyFileName, ''''), ';', 1)

  FROM dbo.MyFileTable

  ) + ';';

  PRINT @sql

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato