SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

davidreed
Starting Member

USA
7 Posts

Posted - 01/03/2006 :  01:49:35  Show Profile  Reply with Quote
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

United Kingdom
22403 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):

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

Kristen

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

davidreed
Starting Member

USA
7 Posts

Posted - 01/03/2006 :  09:26:11  Show Profile  Reply with Quote
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!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

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

Kristen
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
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


rockmoose
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.08 seconds. Powered By: Snitz Forums 2000