Loops within dynamic SQL

Question

I have code that I'd like to apply to a number of tables but rather than simply copy and replace table names, I'd like to use some kind of loop or cursor to simplify things.

I envision setting up an array of my tables names and using an index to iterate over the list, retrieving each table name and using dynamic SQL to intersperse the table name where applicable in my code.

Since there's no 'array' construct, as far as I know, within SQL, I'm not sure how this would work.

Any ideas about how to go about this?

Answer 1

Here is one way of doing it:

--Declare a table variable to hold your table names (and column names in case needed)
declare @listOfTablesToUpdate table (tableName varchar(100), columnNameToUpdate varchar(50))

--insert the tables that you want to work with.
insert into @listOfTablesToUpdate values ('Table1', 'column2')
insert into @listOfTablesToUpdate values ('Table2', 'column3')
insert into @listOfTablesToUpdate values ('Table3', 'column4')

--Cursor for iterating
declare @tableCursor cursor,
        @tableName varchar(100),
        @columnName varchar(50)

set @tableCursor = cursor for select * from @listOfTablesToUpdate

open @tableCursor
fetch next from @tableCursor into @tableName, @columnName
while(@@fetch_status = 0)
begin
    --dynamic sql
    declare @sql varchar(max)

    --Your logic here...this is just an example
    set @sql = 'update '+@tableName+' set '+@columnName+' = '+<value>+' where '+@columnName +' = '+<someothervalue>
    exec @sql

    fetch next from @tableCursor into @tableName, @columnName
end

close @tableCursor
deallocate @tableCursor

Answer 2

Another approach involves preparing a helper function and a procedure that allow one to apply different SQL statements to each object (table, database, et cetera) in a list. The helper function comes from a SSRS Parameter question and splits apart a comma delimited list into a table.

-- from https://stackoverflow.com/questions/512105/passing-multiple-values-for-a-single-parameter-in-reporting-services
CREATE FUNCTION [dbo].[fn_MVParam]
   (@RepParam NVARCHAR(4000), @Delim CHAR(1)= ',')
RETURNS @Values TABLE (Param NVARCHAR(4000))AS
  BEGIN
  DECLARE @chrind INT
  DECLARE @Piece NVARCHAR(100)
  SELECT @chrind = 1 
  WHILE @chrind > 0
    BEGIN
      SELECT @chrind = CHARINDEX(@Delim,@RepParam)
      IF @chrind  > 0
        SELECT @Piece = LEFT(@RepParam,@chrind - 1)
      ELSE
        SELECT @Piece = @RepParam
      INSERT  @Values(Param) VALUES(CAST(@Piece AS VARCHAR))
      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
      IF LEN(@RepParam) = 0 BREAK
    END
  RETURN
  END
GO

Below is the code for the ProcessListSQL procedure.

-- @SQL to execute shall include {RP} as the replacement expression that
-- will evaluate to all the items in the comma delimited list
-- Also, please include a double quote " rather than two single quotes ''
-- in the input statement.

CREATE PROCEDURE [dbo].[ProcessListSQL]  (
    @CommaDelimitedList AS NVARCHAR(MAX),
    @SQLtoExecute AS NVARCHAR(MAX) )
AS BEGIN

DECLARE @Statements TABLE
(   PK INT IDENTITY(1,1) PRIMARY KEY,
    SQLObject NVARCHAR (MAX)
)

SET @SQLtoExecute = REPLACE (@SQLtoExecute, '"', '''')

INSERT INTO @Statements
SELECT PARAM FROM [dbo].[fn_MVParam](@CommaDelimitedList,',')

DECLARE @i INT
SELECT @i = MIN(PK) FROM @Statements
DECLARE @max INT
SELECT @max = MAX(PK) FROM @Statements

DECLARE @SQL AS NVARCHAR(MAX) = NULL
DECLARE @Object AS NVARCHAR(MAX) = NULL

WHILE @i <= @max 
    BEGIN       
        SELECT @Object = SQLObject FROM @Statements WHERE PK = @i       
        SET @SQL = REPLACE(@SQLtoExecute, '{RP}', @Object)

        -- Uncommend below to check the SQL
        -- PRINT @SQL

        EXECUTE sp_executesql @SQL

        SELECT @Object = NULL
        SELECT @SQL = NULL
        SET @i = @i + 1
    END
END      
GO

The ProcessListSQL procedure take two parameters. The first is a comma delimited string that contains the list of objects that will be cycled through. The second parameter is a string that contains the SQL that will be executed with each of the objects in the first parameter.

In the below example, four databases are created. Note that {rp} is replaced with each of the objects in the first parameter and double quotes are needed in each place where single quotes are needed in the SQL statement.

EXECUTE ProcessListSQL 'rice,apples,cheese,tomatos',
'CREATE DATABASE [{rp}] CONTAINMENT = NONE 
        ON  PRIMARY ( NAME = N"{rp}", 
        FILENAME = N"D:\data\user\{rp}.mdf" , 
        SIZE = 4096KB , 
        FILEGROWTH = 1024KB ) 
        LOG ON 
    ( NAME = N"{rp}_log", 
        FILENAME = N"D:\DATA\USER\{rp}_log.ldf" , 
        SIZE = 1024KB , 
        FILEGROWTH = 10%)'