存储过程问题，大力快进！！！！急，急，急！！！！！

[gjqdsdcn]

这样一个存储过程,测试时通过

CREATE PROCEDURE pro_tanwei_sel_total_sum_test @tanweileixing char(8) AS

DECLARE @sql varchar(8000)
SET @sql = 'select '
SELECT @sql = @sql + 'sum(tanweishunum) as 合计 '

SELECT @sql = @sql + ',sum(case qishu when''一期'' then tanweishunum else 0 end) as 一期合计'



SELECT @sql = @sql + ',sum (case when zhanqu=''' + zhanqu + ''' and tanweileixing<>''按面积计算'' then tanweishunum
when zhanqu=''' + zhanqu + ''' and tanweileixing=''按面积计算'' then pingfangmi
else 0 end) as ['
+ zhanqu + '('+qishu+')]'
FROM (SELECT DISTINCT zhanqu,qishu
FROM department where qishu='一期') a order by qishu desc
SELECT @sql = @sql + ',sum(case qishu when''二期'' then tanweishunum else 0 end) as 二期合计'

SELECT @sql = @sql + ',sum (case when zhanqu=''' + zhanqu + ''' and tanweileixing<>''按面积计算'' then tanweishunum
when zhanqu=''' + zhanqu + ''' and tanweileixing=''按面积计算'' then pingfangmi
else 0 end) as ['
+ zhanqu + '('+qishu+')]'
FROM (SELECT DISTINCT zhanqu,qishu
FROM department where qishu='二期') b order by qishu desc


SELECT @sql = @sql + ',sum(case qishu when''新馆试用'' then tanweishunum else 0 end) as 新馆试用合计'

SELECT @sql = @sql + ',sum (case when zhanqu=''' + zhanqu + ''' and tanweileixing<>''按面积计算'' then tanweishunum
when zhanqu=''' + zhanqu + ''' and tanweileixing=''按面积计算'' then pingfangmi
else 0 end) as ['
+ zhanqu + '('+qishu+')]'
FROM (SELECT DISTINCT zhanqu,qishu
FROM department where qishu='新馆试用') c order by qishu desc

SELECT @sql = @sql + ' from department where tanweixingzhi=''' + @tanweileixing +''''





print @sql
exec(@sql)

GO
可当qishu字段分类较多时，出现下列错误：


select sum(tanweishunum) as 合计 ,sum(case qishu when'一期' then tanweishunum else 0 end) as 一期合计,sum (case when zhanqu='20号馆8层面料（每m2）' and tanweileixing<>'按面积计算' then tanweishunum
when zhanqu='20号馆8层面料（每m2）' and tanweileixing='按面积计算' then pingfangmi
else 0 end) as [20号馆8层面料（每m2）(一期 )],sum (case when zhanqu='矿产冶金洽谈厅' and tanweileixing<>'按面积计算' then tanweishunum
when zhanqu='矿产冶金洽谈厅' and tanweileixing='按面积计算' then pingfangmi
else 0 end) as [矿产冶金洽谈厅(一期 )],sum (case when zhanqu='棚下（每m2)' and tanweileixing<>'按面积计算' then tanweishunum
when zhanqu='棚下（每m2)' and tanweileixing='按面积计算' then pingfangmi
else 0 end) as [棚下（每m2)(一期 )],sum (case when zhanqu='办公文具' and tanweileixing<>'按面积计算' then tanweishunum
when zhanqu='办公文具' and tanweileixing='按面积计算' then pingfangmi
else 0 end) as [办公文具(二期 )],sum (case when zhanqu='编织品（琶洲）' and tanweileixing<>'按面积计算' then tanweishunum
when zhanqu='编织品（琶洲）' and tanweileixing='按面积计算' then pingfangmi
else 0 end) as [编织品（琶洲）(二期 )],sum (case when zhanqu='灯具灯饰' and tanweileixing<>'按面积计算' then tanweishunum
when zhanqu='灯具灯饰' and tanweileixing='按面积计算' then pingfangmi
else 0 end) as [灯具灯饰(二期 )],sum (case when zhanqu='家具' and tanweileixing<>'按面
服务器: 消息 105，级别 15，状态 1，行 55
Unclosed quotation mark before the character string '按面'.
服务器: 消息 170，级别 15，状态 1，行 55
Line 55: Incorrect syntax near '按面'.


原因何在！！！！急，急 ，急 ！！！！！！

[pengdali]

字符不够长！你不要浪费字符：

CREATE PROCEDURE pro_tanwei_sel_total_sum_test @tanweileixing char(8) AS

DECLARE @sql varchar(8000)
SET @sql = 'select '
SELECT @sql = @sql + 'sum(tanweishunum) as 合计 '

SELECT @sql = @sql + ',sum(case qishu when''一期'' then tanweishunum else 0 end) as 一期合计'



SELECT @sql = @sql + ',sum (case when zhanqu=''' + zhanqu + ''' and tanweileixing<>''按面积计算'' then tanweishunum when zhanqu=''' + zhanqu + ''' and tanweileixing=''按面积计算'' then pingfangmi else 0 end) ['+ zhanqu+'('+qishu+')]'
FROM (SELECT DISTINCT zhanqu,qishu FROM department where qishu='一期') a order by qishu desc
SELECT @sql = @sql + ',sum(case qishu when''二期'' then tanweishunum else 0 end) as 二期合计'

SELECT @sql = @sql + ',sum (case when zhanqu=''' + zhanqu + ''' and tanweileixing<>''按面积计算'' then tanweishunum when zhanqu=''' + zhanqu + ''' and tanweileixing=''按面积计算'' then pingfangmi else 0 end) as ['+ zhanqu + '('+qishu+')]'
FROM (SELECT DISTINCT zhanqu,qishu FROM department where qishu='二期') b order by qishu desc


SELECT @sql = @sql + ',sum(case qishu when''新馆试用'' then tanweishunum else 0 end) as 新馆试用合计'

SELECT @sql = @sql + ',sum (case when zhanqu=''' + zhanqu + ''' and tanweileixing<>''按面积计算'' then tanweishunum when zhanqu=''' + zhanqu + ''' and tanweileixing=''按面积计算'' then pingfangmi else 0 end) as ['+ zhanqu + '('+qishu+')]' FROM (SELECT DISTINCT zhanqu,qishu
FROM department where qishu='新馆试用') c order by qishu desc


print @sql + ' from department where tanweixingzhi=''' + @tanweileixing +''''
exec(@sql + ' from department where tanweixingzhi=''' + @tanweileixing +'''')

GO

[liuyun2003]

有点晕啊。你看一下处理流程，当它出现问题的时候，是在什么情况下出现的，是语句的问题，还是数据的问题。找到是那方面出的问题就好办了。

[friendliu]

你的存储过程比较大。你用跟踪试一下，看看有问题出现在哪。

[愉快的登山者]

应该是语句被截断了。
检查连接参数，如网络包大小可能不够长。

[gjqdsdcn]

是字符不够长！可是该怎样处理？大力的方法只是节约了少量的字符！！

[gjqdsdcn]

能不能用几个字符串变量组成，例：
@sql1="...."
@sql2="...."
@sql3="...."
exec(@sql1 + @sql2 + @sql3)???
可是不行！
是字符串变量连接有问题吗？