... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)


SQL Server 2000 vs Executing Text Values as SQL

In SQL Server 2000, it is possible to execute a long SQL statement that is stored in a text column in a table.  It is just ugly.  Take a look:

declare @s0 varchar(8000)
declare @s1 varchar(8000)
...boring lines omitted...
declare @s18 varchar(8000)
declare @s19 varchar(8000)
select @s0=substring(sqlText, 1, 8000)
, @s1=substring(sqlText, 1+8000, 8000)
...more boring lines omitted...
, @s18=substring(sqlText, 1+18*8000, 8000)
, @s19=substring(sqlText, 1+19*8000, 8000)
from tableWithSqlInIt
exec (@s0+@s1+@s2+@s3+@s4+@s5+@s6+@s7+@s8+@s9+@s10+@s11+@s12+@s13+@s14+@s15+@s16+@s17+@s18+@s19)

The exhibited statement only works for text columns whose values are no longer than 160,000 characters (20 x 8000).  Adjust to taste.  Also, if you are using NTEXT columns, then you must use NVARCHAR(4000) and segment the value into blocks of 4000 to avoid overrunning SQL Server's 8000 byte limit.

Blog Archive