Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 Execute massive SQL statement

Author  Topic 

Starting Member

7 Posts

Posted - 2006-01-03 : 01:49:35

I thought this was a neat solution I came up with, but I'm sure it's
been thought of before. Anyway, it's my first post here.

We have a process for importing data which generates a SELECT statement
based on user's stored configuration. Since the resulting SELECT statement
can be massive, it's created and stored in a text field in a temp table.

So how do I run this huge query after creating it? In my tests, I was
getting a datalength > 20000, requiring 3 varchar(8000) variables in
order to use the execute command. Thing is, I don't know how big it could
possibly get, I wanted to be able to execute it regardless.

Here's what I came up with, it's very simple:

Table is named #IMPORTQUERY, one field SQLTEXT of type TEXT.

declare @x int, @s varchar(8000)

select @x = datalength(sqltext) / 8000 + 1, @s = 'execute('''')' from #importquery

while @x > 0
select @s = 'declare @s' + cast(@x as varchar) + ' varchar(8000) ' +
'select @s' + cast(@x as varchar) +
'=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery ' +
replace(@s,'execute(','execute(@s' + cast(@x as varchar) + '+')
, @x = @x - 1

set @s = 'declare @x int set @x=1 ' + @s


At the end, I execute the "@s" variable which is SQL that builds and
executes the massive query. Here's what @s looks like at the end:

declare @x int set @x=1
declare @s1 varchar(8000)
select @s1=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery
declare @s2 varchar(8000)
select @s2=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery
declare @s3 varchar(8000)
select @s3=substring(sqltext,@x,@x+8000),@x=@x+8000 from #importquery


22859 Posts

Posted - 2006-01-03 : 08:36:47
Hi davidreed, Welcome to SQL Team!

You could also construct the SQL into a number of varchar(8000) variables (albeit with a hard-wired limit on the number):

Go to Top of Page

Starting Member

7 Posts

Posted - 2006-01-03 : 09:26:11
Thanks for the reply. In fact, that's how I had it written, using (an arbitrary number of) 10 varchar(8000) vars.
This new solution will generate as many varchar(8000)'s as necessary. Well, at ~110 bytes per var for the resulting code, would actually have a max of around 70 varchar(8000)'s returned, for a total possible SQL length of > 500,000 bytes. At that point I'd put the brakes on anyway...

Go to Top of Page


22859 Posts

Posted - 2006-01-03 : 10:30:49
Sounds good enough to me too!

Go to Top of Page

SQL Natt Alfen

3279 Posts

Posted - 2006-01-03 : 14:56:45
Hi davidreed,

The first dynamic sql working on a text field that i have seen

There is also a undocumented proc in sql server "sp_execresultset".
Works a bit differently, but can execute very large resultsets.

create table #x(code varchar(8000))
insert #x
select 'select 1'
union all select 'union select 2'
union all select 'union select 3'
union all select 'union select 4'

--select code from #x

exec sp_execresultset @cmd = 'select code from #x'

drop table #x

Go to Top of Page

- Advertisement -