这个存储过程的查询条件是动态的,执行后提示在%附近有错误,我的目的是想给查询结果中加入一个'%'CREATE procedure PJRKTJ
@QryCon VARCHAR(500)
AS
DECLARE @S VARCHAR(1000)
SET @S = 'SELECT E.JC,E.PJMC AS 零件名称, E.PJDM as 零件代码, E.CX as 车型, E.CD as 产地,
A.入库次数 AS 入库次数,A.入库数量 AS 入库数量,A.入库金额 AS 入库金额,
A.最高成本价 AS 最高成本价,A.最低成本价 AS 最低成本价,A.平均成本价 AS 平均成本价,
CASE WHEN C.入库总数量>0 THEN CAST(CAST(A.入库数量/C.入库总数量*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 入库数量百分比,
CASE WHEN C.入库总金额>0 THEN CAST(CAST(A.入库金额/C.入库总金额*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 入库金额百分比,
ISNULL(B.出库次数,0) AS 出库次数,ISNULL(B.出库数量,0) AS 出库数量,ISNULL(B.出库金额,0) AS 出库金额,
ISNULL(B.最高售价,0) AS 最高售价,ISNULL(B.最低售价,0) AS 最低售价,ISNULL(B.平均售价,0) AS 平均售价,
CASE WHEN D.出库总数量>0 THEN CAST(CAST(B.出库数量/D.出库总数量*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 出库数量百分比,
CASE WHEN D.出库总金额>0 THEN CAST(CAST(B.出库金额/D.出库总金额*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 出库金额百分比
from
(
select CAST(ISNULL(sum(sl),0) AS numeric) as 入库总数量,IsNull(sum(sl*cbj),0) as 入库总金额
from t_0102
where rkdh in (select rkdh from t_0101 where '+@QryCon+')
) C,
(
select CAST(IsNull(sum(sl),0) AS numeric) as 出库总数量,IsNull(sum(sl*dj),0) as 出库总金额
From t_0202
where ckdh in (select ckdh from t_0201 '+@QryCon+')
) D,
(
select pjbm,
COUNT(pjbm) as 入库次数,
SUM(SL) AS 入库数量,
SUM(CBJ*SL) AS 入库金额,
Cast(AVG(CBJ) as decimal(10,2)) AS 平均成本价,
MAX(CBJ) AS 最高成本价,
MIN(CBJ) AS 最低成本价
FROM T_0102
where rkdh in (select rkdh from t_0101 where '+@QryCon+')
GROUP BY PJBM
) A LEFT JOIN
(
select pjbm,
count(pjbm) as 出库次数,
sum(sl) AS 出库数量,
SUM(DJ*SL) AS 出库金额,
Cast(AVG(DJ) as decimal(10,2)) AS 平均售价,
MAX(DJ) AS 最高售价,
MIN(DJ) AS 最低售价
FROM T_0202
where ckdh in (select ckdh from t_0201 where '+@QryCon+')
GROUP BY PJBM
) B ON A.PJBM=B.PJBM
LEFT JOIN
(
SELECT PJBM,JC,PJMC,PJDM,CX,CD FROM T_0103
) E ON A.PJBM=E.PJBM'
exec(@s)
GO
@QryCon VARCHAR(500)
AS
DECLARE @S VARCHAR(1000)
SET @S = 'SELECT E.JC,E.PJMC AS 零件名称, E.PJDM as 零件代码, E.CX as 车型, E.CD as 产地,
A.入库次数 AS 入库次数,A.入库数量 AS 入库数量,A.入库金额 AS 入库金额,
A.最高成本价 AS 最高成本价,A.最低成本价 AS 最低成本价,A.平均成本价 AS 平均成本价,
CASE WHEN C.入库总数量>0 THEN CAST(CAST(A.入库数量/C.入库总数量*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 入库数量百分比,
CASE WHEN C.入库总金额>0 THEN CAST(CAST(A.入库金额/C.入库总金额*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 入库金额百分比,
ISNULL(B.出库次数,0) AS 出库次数,ISNULL(B.出库数量,0) AS 出库数量,ISNULL(B.出库金额,0) AS 出库金额,
ISNULL(B.最高售价,0) AS 最高售价,ISNULL(B.最低售价,0) AS 最低售价,ISNULL(B.平均售价,0) AS 平均售价,
CASE WHEN D.出库总数量>0 THEN CAST(CAST(B.出库数量/D.出库总数量*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 出库数量百分比,
CASE WHEN D.出库总金额>0 THEN CAST(CAST(B.出库金额/D.出库总金额*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 出库金额百分比
from
(
select CAST(ISNULL(sum(sl),0) AS numeric) as 入库总数量,IsNull(sum(sl*cbj),0) as 入库总金额
from t_0102
where rkdh in (select rkdh from t_0101 where '+@QryCon+')
) C,
(
select CAST(IsNull(sum(sl),0) AS numeric) as 出库总数量,IsNull(sum(sl*dj),0) as 出库总金额
From t_0202
where ckdh in (select ckdh from t_0201 '+@QryCon+')
) D,
(
select pjbm,
COUNT(pjbm) as 入库次数,
SUM(SL) AS 入库数量,
SUM(CBJ*SL) AS 入库金额,
Cast(AVG(CBJ) as decimal(10,2)) AS 平均成本价,
MAX(CBJ) AS 最高成本价,
MIN(CBJ) AS 最低成本价
FROM T_0102
where rkdh in (select rkdh from t_0101 where '+@QryCon+')
GROUP BY PJBM
) A LEFT JOIN
(
select pjbm,
count(pjbm) as 出库次数,
sum(sl) AS 出库数量,
SUM(DJ*SL) AS 出库金额,
Cast(AVG(DJ) as decimal(10,2)) AS 平均售价,
MAX(DJ) AS 最高售价,
MIN(DJ) AS 最低售价
FROM T_0202
where ckdh in (select ckdh from t_0201 where '+@QryCon+')
GROUP BY PJBM
) B ON A.PJBM=B.PJBM
LEFT JOIN
(
SELECT PJBM,JC,PJMC,PJDM,CX,CD FROM T_0103
) E ON A.PJBM=E.PJBM'
exec(@s)
GO
exec(@s) ----------------------> print @s
把动态生成的SQL打印出来一看便之!
(
select CAST(ISNULL(sum(sl),0) AS numeric) as 入库总数量,IsNull(sum(sl*cbj),0) as 入库总金额
from t_0102
where rkdh in (select rkdh from t_0101 where '+@QryCon+')
) C,
(
select CAST(IsNull(sum(sl),0) AS numeric) as 出库总数量,IsNull(sum(sl*dj),0) as 出库总金额
From t_0202
where ckdh in (select ckdh from t_0201 '+@QryCon+') //-------------->缺少Where
) D,
CASE WHEN C.入库总金额>0 THEN CAST(CAST(A.入库金额/C.入库总金额*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 入库金额百分比,,//----------------1个CAST做类型转换就行了,用2个做什么?????且还有问题!写这么乱,我都快崩溃了...
CASE WHEN C.入库总金额>0 THEN CAST(CAST(A.入库金额/C.入库总金额*100 AS numeric(5, 2)) AS VARCHAR) +''%'' ELSE 0 END AS 入库金额百分比