Execute massive SQL statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. 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

davidreed
Starting Member

7 Posts

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

Hello,

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

  execute(@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 
execute(@s1+@s2+@s3+'')
<<

Kristen
Test

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):

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Execute+dynamic+SQL+that+is+longer+than+varchar(8000)

Kristen

davidreed
Starting Member

7 Posts

Posted - 2006-01-03 : 09:26:11

Kristen,
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...

Cheers!

Kristen
Test

22859 Posts

Posted - 2006-01-03 : 10:30:49

Sounds good enough to me too!

Kristen

rockmoose
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

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources