Loops in dynamic SQL

Question

I have code that I would like to apply to multiple tables, but instead of just copying and replacing the table names, I would like to use some kind of loop or cursor to simplify.

I foresee creating an array of the names of my tables and using an index to iterate through the list, extracting each table name and using dynamic SQL to intersect the table name, where applicable in my code.

Since there is no array construct in SQL, as far as I know, I'm not sure how this will work.

Any ideas on how to do this?

Answer 1

Here is one way to do this:

--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 an auxiliary function and a procedure that allows you to apply different SQL statements to each object (table, database, etc.) in the list. The helper function starts from the SSRS Parameter question and splits the comma-delimited list into a table.

 -- from /questions/92054/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 

The following 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 takes two parameters. The first is a comma-separated string that contains a list of objects that will loop through. The second parameter is a line containing SQL that will be executed with each of the objects in the first parameter.

The following example creates four databases. Note that {rp} is replaced by each of the objects in the first parameter, and in every place where you need single quotes in the SQL statement, double quotes are needed.

 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%)'