化解字符串不能超过8000的方法

/*--

化解字符串不能超过8000的方法

 

经常有人提到，用动态生成SQL语句的方法处理数据时，处理语句超长，无法处理的问题，下面就讨论这个问题：

测试环境：

--以系统表 syscolumns 为测试数据，要求按xtype为列字段，name为行字段，统计colid的和

--要求结果

xtype    filedname_1    fieldname_2    .....    fieldname_n

-------- -------------- -------------- -------- --------------

34       0              0              .....    1

--*/

 

/*--常规处理方法(不加行数限制会因生成的字符串益出而出错)

set rowcount 10--因为syscolumns的记录较大，会导致生成的字符串溢出，所以限制一下行数

 

declare @s nvarchar(4000)

set @s=''

select @s=@s+N'，'+quotename([name])

+N'=sum(case name when '+quotename([name]，'''')

+N' then [colid] else 0 end)'

from(select distinct [name] from [syscolumns]) a

 

set rowcount 0--取消限制

exec(N'select [xtype]'+@s+N' from [syscolumns] group by [xtype]')

 

/*--问题

不加行数限制时，会因生成的字符串益出而出错

--*/

--*/

 

/*--方法1. 多个变量处理

 

--根据查询结果定义变量(实际处理中，应该是估计需要多少个变量，定义足够多的变量个数，多定义变量并不影响处理，下面就多定义了一个)

--生成数据处理临时表

SELECT id=IDENTITY(int，0，1)，

g=0，

a=CAST(N'，'+QUOTENAME([name])

+N'=SUM(CASE [name] WHEN N'+QUOTENAME(name，N'''')

+N' THEN [colid] ELSE 0 END)'

as nvarchar(4000))

INTO # FROM syscolumns

WHERE name>N''

GROUP BY name

 

--分组临时表

UPDATE a SET G=id/i

FROM # a，(SELECT i=3800/MAX(LEN(a)) FROM #)b

SELECT MAX(g)+1 as N'需要的变量个数' FROM #

 

DECLARE @0 nvarchar(4000)，@1 nvarchar(4000)，@2 nvarchar(4000)，@3 nvarchar(4000)，@4 nvarchar(4000)

SELECT @0=N''，@1=N''，@2=N''，@3=N''，@4=N''

SELECT

@0=CASE g WHEN 0 THEN @0+a ELSE @0 END，

@1=CASE g WHEN 1 THEN @1+a ELSE @1 END，

@2=CASE g WHEN 2 THEN @2+a ELSE @2 END，

@3=CASE g WHEN 3 THEN @3+a ELSE @3 END，

@4=CASE g WHEN 4 THEN @4+a ELSE @4 END

FROM #

EXEC(N'SELECT xtype'+@0+@1+@2+@3+@4+N' FROM syscolumns GROUP BY xtype')

DROP TABLE #

 

/*--方法说明

 

优点：比较灵活，数据量大时只需要增加变量就行了.不用改动其他部分

缺点：要自行估计处理的数据，估计不足就会出错

--*/

--*/

 

/*--方法2. bcp+isql

 

--因为要用到bcp+isql，所以需要这些信息

declare @servername sysname，@username sysname，@pwd sysname

select @servername=@@servername  --服务器名

，@username=N''           --用户名

，@pwd=N''                --密码

 

declare @tbname sysname，@s nvarchar(4000)

 

--创建数据处理临时表

set @tbname=quotename(N'##temp_'+cast(newid() as varchar(36)))

set @s=N'create table '+@tbname+'(a nvarchar(4000))

insert into '+@tbname+N'

select N''create view '

+stuff(@tbname，2，2，N'')

+N' as

select [xtype]''

union all

select N''，''+quotename([name])+''=sum(case [name] when N''

+quotename([name]，'''''''')

+'' then [colid] else 0 end)''

from(select distinct [name] from [syscolumns] where name<>N''xtype'')a

union all

select N''from [syscolumns] group by [xtype]'''

exec(@s)

 

--生成创建视图的文件，注意使用了文件：c：\temp.txt

set @s=N'bcp "'+@tbname+N'" out "c：\'+@tbname+N'" /S"'

+@servername+N'" /U"'+@username+N'" /P"'+@pwd+N'" /w'

exec master..xp_cmdshell @s，no_output

 

--调用isql生成数据处理视图

set @s=N'osql /S"'+@servername

+case

when @username=N'' then N'" /E'

else N'" /U"'+@username+N'" /P"'+@pwd+N'"'

end

+N' /d"'+db_name()+N'" /i"c：\'+@tbname+'"'

exec master..xp_cmdshell @s，no_output

 

--删除临时文件

set @s=N'del "c：\'+@tbname+'"'

exec master..xp_cmdshell @s，no_output

 

--调用视图，显示处理结果

set @s=N'drop table '+@tbname+N'

select * from '+stuff(@tbname，2，2，N'')+N'

drop view '+stuff(@tbname，2，2，N'')

exec(@s)

 

/*--方法总结

 

优点：程序自动处理，不存在判断错误的问题

缺点：复杂，经过的步骤多，容易出错，而且需要一定的操作员权限

--*/

--*/

 

--/*-- 方法3. 多个变量处理，综合了方法1，2的优点，解决了方法1中需要人为判断，增加变量的问题，排除了方法2，需要权限和过程复杂的问题

DECLARE @sqlhead nvarchar(4000)，@sqlend nvarchar(4000)

，@sql1 nvarchar(4000)，@sql2 nvarchar(4000)，@sql3 nvarchar(4000)，@sql4 nvarchar(4000)

，@i int，@ic nvarchar(10)

 

--生成数据处理临时表

SELECT id=IDENTITY(int，0，1)，

g=0，

a=CAST(N'，'

+QUOTENAME([name])

+N'=SUM(CASE [name] WHEN N'

+QUOTENAME(name，N'''')

+N' THEN [colid] ELSE 0 END)'

as nvarchar(4000))

INTO # FROM(

SELECT DISTINCT name FROM [syscolumns] WHERE name>N'')a

 

--分组临时表

UPDATE a SET @i=id/i，g=@i

FROM # a，(SELECT i=3800/MAX(LEN(a)) FROM #)b

SET @ic=@i

 

--生成数据处理语句

SELECT

@sqlhead=N''''

+REPLACE(N'SELECT [xtype]'，N''''，N'''''')

+''''，

@sqlend=N''''

+REPLACE(N' FROM [syscolumns] GROUP BY [xtype]'，N''''，N'''''')

+N''''，

@sql1=N''，@sql2=N''，@sql3=N''，@sql4=N''

WHILE @ic>=0

SELECT

@sql1=N'，@'+@ic+N' nvarchar(4000)'+@sql1，

@sql2=N'，@'+@ic+N'=N'''''+@sql2，

@sql3=N'，@'+@ic

+N'=CASE g WHEN '+@ic

+N' THEN @'+@ic+N'+a ELSE @'+@ic

+N' END'+@sql3，

@sql4=N'+@'+@ic+@sql4，

@ic=@ic-1

SELECT

@sql1=STUFF(@sql1，1，1，N'')，

@sql2=STUFF(@sql2，1，1，N'')，

@sql3=STUFF(@sql3，1，1，N'')，

@sql4=STUFF(@sql4，1，1，N'')

 

--执行

EXEC(N'DECLARE '+@sql1+N'

SELECT '+@sql2+N'

SELECT '+@sql3+N' FROM #

EXEC(N'+@sqlhead+N'+'+@sql4+N'+N'+@sqlend+N')')

--删除临时表

DROP TABLE #

 

/*--方法总结

 

总结了前两种方法的优点，自动判断需要处理的变量数

--*/

--*/