Sp_Executesql ntext

Question

Post reply

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

5409045121009-7368

SSCoach

Points: 15740

August 20, 2003 at 3:19 pm

#470674

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

WHug

Ten Centuries

Points: 1248

August 20, 2003 at 3:29 pm

#470676

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.