写了一个存储过程，有三个参数，在查询分析器中测试通过，但是在程序中调用时，同样的参数却会出错！use qc
if exists (select name from sysobjects where name='P_PJRKTJ_RK_XSCK_WXCK' and type='p')
drop procedure P_PJRKTJ_RK_XSCK_WXCK
go
use qc
gocreate procedure P_PJRKTJ_RK_XSCK_WXCK
@QryCon_RK VARCHAR(500),
@QryCon_CK VARCHAR(500),
@QryCon_WX VARCHAR(500)
AS
declare @SqlString VARCHAR(5000)set @SqlString='SELECT E.JC,E.PJMC AS 零件名称, E.PJDM as 零件代码, E.CX as 车型, E.CD as 产地,
C.入库总数量 AS 入库总数量,C.入库总金额 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 入库金额百分比, (D.出库总数量+F.出库总数量) AS 出库总数量,(D.出库总金额+F.出库总金额) AS 出库总金额,(B.出库次数+G.出库次数) AS 总出库次数, D.出库总数量 AS 销售出库总数量,D.出库总金额 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 销售出库金额占销售出库总金额百分比,
CASE WHEN (D.出库总数量+F.出库总数量)>0 THEN CAST(CAST(B.出库数量/(D.出库总数量+F.出库总数量)*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 销售出库数量占总出库数量百分比,
CASE WHEN (D.出库总金额+F.出库总金额)>0 THEN CAST(CAST(B.出库金额/(D.出库总金额+F.出库总金额)*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 销售出库金额占总出库金额百分比, F.出库总数量 AS 维修出库总数量,F.出库总金额 AS 维修出库总金额,
ISNULL(G.出库次数,0) AS 维修出库次数,ISNULL(G.出库数量,0) AS 维修出库数量,ISNULL(G.出库金额,0) AS 维修出库金额,
ISNULL(G.最高售价,0) AS 维修出库最高价,ISNULL(G.最低售价,0) AS 维修出库最低价,ISNULL(G.平均售价,0) AS 维修出库平均价,
CASE WHEN F.出库总数量>0 THEN CAST(CAST(G.出库数量/F.出库总数量*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 维修出库数量占维修出库总数量百分比,
CASE WHEN F.出库总金额>0 THEN CAST(CAST(G.出库金额/F.出库总金额*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 维修出库金额占维修出库总金额百分比,
CASE WHEN (D.出库总数量+F.出库总数量)>0 THEN CAST(CAST(G.出库数量/(D.出库总数量+F.出库总数量)*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 维修出库数量占总出库数量百分比,
CASE WHEN (D.出库总金额+F.出库总金额)>0 THEN CAST(CAST(G.出库金额/(D.出库总金额+F.出库总金额)*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 '+@QryCon_RK+')) 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_CK+')
) D, (
select CAST(IsNull(sum(sl),0) AS numeric) as 出库总数量,IsNull(sum(sl*dj),0) as 出库总金额
from t_0403
where WXDH in (select wxdh from t_0401 '+@QryCon_WX+')
) F, (
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 '+@QryCon_RK+')
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 '+@QryCon_CK+')
GROUP BY PJBM
) B ON A.PJBM=B.PJBM 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_0403
where wxdh in (select wxdh from t_0401 '+@QryCon_WX+')
GROUP BY PJBM
) G ON A.PJBM=G.PJBM LEFT JOIN
(
SELECT PJBM,JC,PJMC,PJDM,CX,CD FROM T_0103
) E ON A.PJBM=E.PJBM'
exec(@SqlString)
if exists (select name from sysobjects where name='P_PJRKTJ_RK_XSCK_WXCK' and type='p')
drop procedure P_PJRKTJ_RK_XSCK_WXCK
go
use qc
gocreate procedure P_PJRKTJ_RK_XSCK_WXCK
@QryCon_RK VARCHAR(500),
@QryCon_CK VARCHAR(500),
@QryCon_WX VARCHAR(500)
AS
declare @SqlString VARCHAR(5000)set @SqlString='SELECT E.JC,E.PJMC AS 零件名称, E.PJDM as 零件代码, E.CX as 车型, E.CD as 产地,
C.入库总数量 AS 入库总数量,C.入库总金额 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 入库金额百分比, (D.出库总数量+F.出库总数量) AS 出库总数量,(D.出库总金额+F.出库总金额) AS 出库总金额,(B.出库次数+G.出库次数) AS 总出库次数, D.出库总数量 AS 销售出库总数量,D.出库总金额 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 销售出库金额占销售出库总金额百分比,
CASE WHEN (D.出库总数量+F.出库总数量)>0 THEN CAST(CAST(B.出库数量/(D.出库总数量+F.出库总数量)*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 销售出库数量占总出库数量百分比,
CASE WHEN (D.出库总金额+F.出库总金额)>0 THEN CAST(CAST(B.出库金额/(D.出库总金额+F.出库总金额)*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 销售出库金额占总出库金额百分比, F.出库总数量 AS 维修出库总数量,F.出库总金额 AS 维修出库总金额,
ISNULL(G.出库次数,0) AS 维修出库次数,ISNULL(G.出库数量,0) AS 维修出库数量,ISNULL(G.出库金额,0) AS 维修出库金额,
ISNULL(G.最高售价,0) AS 维修出库最高价,ISNULL(G.最低售价,0) AS 维修出库最低价,ISNULL(G.平均售价,0) AS 维修出库平均价,
CASE WHEN F.出库总数量>0 THEN CAST(CAST(G.出库数量/F.出库总数量*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 维修出库数量占维修出库总数量百分比,
CASE WHEN F.出库总金额>0 THEN CAST(CAST(G.出库金额/F.出库总金额*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 维修出库金额占维修出库总金额百分比,
CASE WHEN (D.出库总数量+F.出库总数量)>0 THEN CAST(CAST(G.出库数量/(D.出库总数量+F.出库总数量)*100 AS numeric(5, 2)) AS VARCHAR)+''%'' ELSE ''0'' END AS 维修出库数量占总出库数量百分比,
CASE WHEN (D.出库总金额+F.出库总金额)>0 THEN CAST(CAST(G.出库金额/(D.出库总金额+F.出库总金额)*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 '+@QryCon_RK+')) 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_CK+')
) D, (
select CAST(IsNull(sum(sl),0) AS numeric) as 出库总数量,IsNull(sum(sl*dj),0) as 出库总金额
from t_0403
where WXDH in (select wxdh from t_0401 '+@QryCon_WX+')
) F, (
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 '+@QryCon_RK+')
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 '+@QryCon_CK+')
GROUP BY PJBM
) B ON A.PJBM=B.PJBM 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_0403
where wxdh in (select wxdh from t_0401 '+@QryCon_WX+')
GROUP BY PJBM
) G ON A.PJBM=G.PJBM LEFT JOIN
(
SELECT PJBM,JC,PJMC,PJDM,CX,CD FROM T_0103
) E ON A.PJBM=E.PJBM'
exec(@SqlString)
a,b,c:string;
begin
....
ADODataSet1.Close;
ADODataSet1.CommandText:='exec P_PJRKTJ_RK_XSCK_WXCK '+quotedstr(a)+','+quotedstr(b)+','+quotedstr(c)+'';
ADODataSet1.Open;
end;