Sp_Executesql ntext

Question

Sp_Executesql ntext

WHug

Ten Centuries

Points: 1248
More actions
August 20, 2003 at 2:52 pm

#101635

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'

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

5409045121009-7368 SSCoach Points: 15740 More actions · Answer 1

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)

WHug Ten Centuries Points: 1248 More actions · Answer 2

Thanks for your help. Apparently sp_executesql will not allow the concatenation, unless you know a way.

Keith Henry Hall of Fame Points: 3759 More actions · Answer 3

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

According to everyone I know I "do something with computers?" for a living, so there you go.