Here is a sproc I wrote to handle a similar problem:
/* This can execute dynamically generated SQL statements
up to approx 156000 chars in length.
Since you can't pass this as a variable the proc takes
the table and fieldname of a text value to execute.
A where statement can be included and should fully
declare the table name. Try to keep this statement as
simple as possible.
This can also handle fields in #temp tables
Author: Keith Henry (k_count at hotmail) */
create proc executelargesql(@tablename sysname, @fieldname sysname, @where nvarchar(3000) = null) as
begin
declare @len int, @isql nvarchar(4000), @i int, @lsql nvarchar(4000)
create table #isqlvars (dlen int, hold ntext) --#table created before sp_executesql will be visible inside it
--first we need to get the length of the field we are dealing with
if left(@tablename, 1) = '#' --we have a # table as source, look in temp db
set @isql = 'declare @type sysname' + char(10) +
'select @type = t.[name] from tempdb.dbo.sysobjects o inner join tempdb.dbo.syscolumns c on o.[id] = c.[id] inner join tempdb.dbo.systypes t on c.xtype = t.xtype where left(o.[name], len(''' + @tablename + ''')) = ''' + @tablename + ''' and c.[name] = ''' + @fieldname + '''' + char(10) +
'insert #isqlvars (dlen,hold) select case @type when ''ntext'' then (datalength([' + @tablename + '].[' + @fieldname + '])/2) else datalength([' + @tablename + '].[' + @fieldname + ']) end,[' + @tablename + '].[' + @fieldname + '] from [' + @tablename + ']' + isnull(' where' + @where,'')
else
set @isql = 'declare @type sysname' + char(10) +
'select @type = t.[name] from dbo.sysobjects o inner join dbo.syscolumns c on o.[id] = c.[id] inner join dbo.systypes t on c.xtype = t.xtype where o.[name] = ''' + @tablename + ''' and c.[name] = ''' + @fieldname + '''' + char(10) +
'insert #isqlvars (dlen,hold) select case @type when ''ntext'' then (datalength(dbo.[' + @tablename + '].[' + @fieldname + '])/2) else datalength(dbo.[' + @tablename + '].[' + @fieldname + ']) end,dbo.[' + @tablename + '].[' + @fieldname + '] from dbo.[' + @tablename + ']' + isnull(' where' + @where,'')
exec sp_executesql @isql
if @@error <> 0 or @@rowcount <> 1 goto doh
select @isql = '', @i = 0, @lsql = null, @len = v.dlen from #isqlvars v
while @i <= @len --this can loop up to 39 times before it becomes too big for sp_executesql to handle
select @isql = @isql + char(10) +
'declare @sql' + cast(@i as varchar) + ' nvarchar(4000)' + char(10) +
'select @sql' + cast(@i as varchar) + ' = substring(hold,' + cast(@i as varchar) + ',4000) from #isqlvars',
@lsql = isnull(@lsql + '+','') + '@sql' + cast(@i as varchar),
@i = @i + 4000
select @isql = @isql + char(10) + 'exec (' + @lsql + ')'
exec sp_executesql @isql
if @@error <> 0 goto doh
goto done
doh:
print 'An error has occured '
print @isql
select * from #isqlvars
done:
drop table #isqlvars
end
go
Keith Henry
Keith Henry