关于您的大作"在查询分析器中调用sqldmo生成脚本--存储过程"的一点疑问

--老大的东西：参考:
  /*--化解字符串不能超过8000的方法

  经常有人提到,用动态生成SQL语句的方法处理数据时,处理语句超长,无法处理的问题
  下面就讨论这个问题:
  --邹建   2003.9(引用请保留此信息)--*/

  /*--   测试环境

  --以系统表   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   #

  /*--方法总结

  总结了前两种方法的优点，自动判断需要处理的变量数
  --*/
  --*/
2005 写 CLR , 参数都有 8000 字节的限制, 除非直接写数据到表里

调试易

关于您的大作"在查询分析器中调用sqldmo生成脚本--存储过程"的一点疑问

解决方案 »