EXEC inside of a WHILE LOOP

Transact-SQL
1

I am using a EXEC inside of a WHILE LOOP. It works fine until looping up to 10x. After that, I was able to track down that it stalls at EXEC. Is this a buffer issue and how would I clear it?

Thanks

2

You would have to post the code. There are many reason why it might "stall".

3

Hi, here is a simplified version of the code. It is stalling on EXEC statement after it loops up to 11x. There is a total of 21 counts in the @tblSQL table.

WHILE EXISTS (SELECT 1 FROM @tblSQL WHERE ISNULL(Execstatus, 0) = 0)
BEGIN
	SELECT TOP 1 
		@tmpTblname = ObjectName
	  , @strSQL_NVAR_MAX = SQLStatement
	FROM @tblSQL 
	WHERE ISNULL(Execstatus, 0) = 0

	UPDATE @tblSQL
	SET Execstatus = 1
	WHERE ObjectName = @tmpTblname

	BEGIN
		------------------------------------------------------------
		IF OBJECT_ID('tempdb..##GlobalTEMPTable') IS NOT NULL
		--IF OBJECT_ID('SearchTMP','U') IS NOT NULL
		BEGIN
			DROP TABLE ##GlobalTEMPTable
			--DROP TABLE SearchTMP
		END
		------------------------------------------------------------
		--PRINT @strSQL_NVAR_MAX	
		EXEC(@strSQL_NVAR_MAX)

		IF EXISTS(SELECT 1 FROM ##GlobalTEMPTable)
		--IF EXISTS(SELECT 1 FROM SearchTMP)
		BEGIN
			SELECT ObjectName=@tmpTblname, * FROM ##GlobalTEMPTable
			--SELECT ObjectName=@tmpTblname,* FROM SearchTMP
			SELECT @blnMatchFound = 1
		END
		DROP TABLE ##GlobalTEMPTable
	END
END
4

And what values are in @strSQL_NVAR_MAX that are being executed?

5

sorry. :slight_smile: It is like:

SELECT * INTO ##GlobalTEMPTable FROM Dynamic_Table_N

6

I have also tried it with
OPTION (OPTIMIZE FOR UNKNOWN)
OPTION (RECOMPILE)

at the end of

SELECT * INTO ##GlobalTEMPTable FROM Dynamic_Table_N

7

First thing I might try is wrapping all of the insert/updates including the exec in begin transaction / commit transaction

8

so what is the story behind this ? What are you attempting to do in plain English without code?

9

BTrimPop, yes, I have tried that too.

Yosiasz, in plain English, I am doing a search for a value on all columns per table (loop). There are a total of 21 tables and it works as plan up to looping 11 times.

I am assuming it has to be a cache issue or some sort.

The same code worked fine on another database but for this database, it is bigger.

10

in one database?

11

Correct! One database, not at server level of all databases.

12

how about this using sys objects

use yourDatabase

go

select 'select * from ' + t.name + ' where ' +  c.name + ' like ''%chicken%''' 
from sys.tables t join sys.columns c on t.object_id = c.object_id

then use the result of the query result first column

13

Yes, I did something like that but I had to loop through each table with a where clause. For that reason, I had to use the EXEC in a loop.

I did add these two and it seems to get me a bit further in the loop.

DBCC DropCleanBuffers
DBCC FREEPROCCACHE

Still not complete and very slow after loop #12.

14

what I posted has a where clause. Is this something you run often or is this a one time thing?

Try this then

select 'select ''' + t.name + ''' from ' + t.name + ' where ' + c.name + ' like ''%chicken%'' union all'
from sys.tables t join sys.columns c on t.object_id = c.object_id

15

Yosiasz, this is an on-going run.

with your above statement, do I still use it with EXEC?

Also, it seems it might be the database. I used my code in several databases now and it is running fine. Maybe it is an index thing on one of the tables.