August 20, 2003 at 2:52 pm
I need to create and execute a dynamic sql statement that is larger then 4000 characters (nvarchar) and could easily grow past 8000 characters (varchar). The solution should be to create the statement in ntext. The question is, how to execute an ntext due to the fact that you can not declare and use a local variable for ntext, text, and image. Here is an example of what I have tried so far:
declare @s1 ntext
set @s1='select loanid from tblloancurrent'
execute sp_executesql @s1
or
create table #testnotes (text1 ntext)
DECLARE @textptr binary(16)
SELECT @textptr=TEXTPTR(text1)
FROM #testnotes (UPDLOCK)
updatetext #testnotes.text1 @textptr 0 0 'select loanid from tblloancurrent'
select * from #testnotes
sp_executesql N'select * from #testnotes'
August 20, 2003 at 3:19 pm
Code adapted from BOL 2000, used to work in 6.5 with the 255 limit.
DECLARE @SQLString1 VARCHAR(8000),
@SQLString2 VARCHAR(8000)
SET @SQLString1 = 'SELECT * FROM Periods WHERE Period_No = 35'
SET @SQLString2 = ''
EXEC (@SQLString1+@SQLString2)
August 20, 2003 at 3:29 pm
Thanks for your help. Apparently sp_executesql will not allow the concatenation, unless you know a way.
August 21, 2003 at 3:20 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply