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.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Execute massive SQL statement
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

7 Posts

Posted - 01/03/2006 :  01:49:35  Show Profile  Reply with Quote

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 


United Kingdom
22859 Posts

Posted - 01/03/2006 :  08:36:47  Show Profile  Reply with Quote
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):


Edited by - Kristen on 01/03/2006 08:37:08
Go to Top of Page

Starting Member

7 Posts

Posted - 01/03/2006 :  09:26:11  Show Profile  Reply with Quote
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


United Kingdom
22859 Posts

Posted - 01/03/2006 :  10:30:49  Show Profile  Reply with Quote
Sounds good enough to me too!

Go to Top of Page

SQL Natt Alfen

3279 Posts

Posted - 01/03/2006 :  14:56:45  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000