--Holds values of the text field broken into 8000 char increments
Create table ##temp(
sequence int identity(1,1),
value varchar(8000)
)
Set nocount on
Declare @execstring varchar(8000)
Declare @maxlength int
select @maxlength=datalength(testtext) from testtable
declare @offset int
Declare @length int
set @length=1
set @offset=1
--Parse through the text field and break it up into chunks and insert into #temp
	While @length<=@maxlength + 3
	begin
	insert into ##temp(value) select substring(testtext,@offset,3) from testtable
	set @offset=@offset + 3
	set @length=@length + 3
	End
select @maxlength=count(*) from ##temp
set @offset=1

Declare @mystring nvarchar(200)
declare @string2 nvarchar(500)
declare @variablestring nvarchar(600)
declare @selects as nvarchar(4000)
declare @finalstring as nvarchar(4000)
declare @workstring nvarchar(4000)
set @string2=''
set @selects=''
set @finalstring=''
set @workstring='    set @finalstring='
set @variablestring='create procedure ##execme as Declare @finalstring nvarchar(4000),'

		--Now, build a temp stored procedure to hold all your dynamic values
		--It's broken into 4 parts
		--1. Variable string, which will hold the top of your stored procedure declaration
		--2. Selects, which query #TEMP to get their values
		--3. Workstring which is the final statement to execute all of your smaller pieces
		--4. Final string pulls the above 3 strings together and creates the procedure.
		while @offset<>@maxlength
		begin
		
		--If it's the final variable, we don't want commas	and seperating punctuation
		if @offset=@maxlength-1
			begin
			--Build the create procedure string
			set @variablestring=@variablestring + ' @myvar' + cast(@offset as varchar(30)) + '  varchar(8000)'
			--Build select statements
			set @selects=@selects + 'select @myvar' + cast(@offset as varchar(30)) + '=value from ##temp where sequence=' + cast(@offset as varchar(30))
			--Build Workstring
			Set @workstring=@workstring + '@myvar' + cast(@offset as varchar(30)) 
			End
		--Otherwise, run this batch to include commas and seperating punctuation
		if @offset<>@maxlength-1
			begin
			--Build the create procedure string
			set @variablestring=@variablestring + ' @myvar' + cast(@offset as varchar(30)) + '  varchar(8000),'
			--Build select statements
			set @selects=@selects + ' select @myvar' + cast(@offset as varchar(30)) + '= value from ##temp where sequence=' + cast(@offset as varchar(30))
			--Build Workstring
			set @workstring=@workstring + '@myvar' + cast(@offset as varchar(30)) + ' + '
			End
		--Increment our counter
		set @offset=@offset + 1
		end
		
		--Tack on a few finishing statements and pull it together
		set @workstring=@workstring + ' exec sp_executesql @finalstring'
		set @finalstring=@variablestring + @selects + @workstring
		
		--Display the finished string to the results window
		print @finalstring
		
		--Build the temporary stored procedure
		exec sp_executesql @finalstring
		
		--Execute the stored procedure 
		exec ##execme
--Clean up
drop table ##temp
drop procedure ##execme
Set Nocount off

 

quote:

---

Using EXECUTE with a Character String
Use the string concatenation operator (+) to create large strings for dynamic execution. Each string expression can be a mixture of Unicode and non-Unicode data types.

Although each [N] ‘tsql_string’ or @string_variable must be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:

EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')

---

-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla...

quote:

---

As a little more elaboration, I'm looking to EXEC a huge SQL command, longer than 8000 characters,...

---

Rob, are you really only looking to execute a SQL command? Taking a really different track, could you do something like this?


-- These are command prompt commands...
-- BCP the Text field out to a plain text file
bcp Northwind..TestText out sqlcmd.txt -T -c

-- Call osql to run the command
osql -E -i sqlcmd.txt -o Results.txt -d Northwind -n

-- the -o parameter puts the output results in a text file.


I did this with a command that was 8132 bytes long. Whaddya think?

-------------------------------------------------------------------
Make mine a Vienti quad-shot half-skinny no foam with vanilla...

USE pubs

CREATE PROCEDURE #readmytext 
                          @ptrval varbinary(16)
			, @offset int
			, @blk_size int
AS
    READTEXT pub_info.pr_info @ptrval @offset @blk_size

go


create table #tmp(lineid int not null identity, formertext varchar (255))


GO
DECLARE @ptrval varbinary(16)
DECLARE @myvar varchar (255)

BEGIN TRAN 

SELECT @ptrval = TEXTPTR(pr_info) 
   FROM pub_info pr WITH (HOLDLOCK) INNER JOIN publishers p
      ON pr.pub_id = p.pub_id 
      AND p.pub_name = 'New Moon Books'

INSERT INTO #temp
EXEC #readmytext @ptrval, 1, 25

commit tran

select @myvar = formertext from #temp 

print '@myvar now equals to -->' +@myvar