存储过程如下,我在调试时使用的调试参数值分别是--
--
-- set @invChkMainFid ='1'
-- set @fdept ='464'
-- set @fcustomer ='2306'
-- set @fet ='17051'
-- set @finvTime ='1997-04-12' --盘点日期
-- set @fpcode ='1' --产品大类编码
-- set @qSql='21' --查询条件
-- set @fzt ='1' --帐套
-- set @find ='1' --产业 ALTER PROCEDURE dbo.InvSum_Prc (
-- 下面有些变量原来是int, 但为了能够组装到SQL语句中就干脆定义成varchar
@invChkMainFid varchar(20) ,
@fdept varchar(20),
@fcustomer varchar(20),
@fet varchar(20),
@finvTime varchar(20), --盘点日期
@fpcode varchar(20), --产品大类编码
@qSql varchar(50), --查询条件
@fzt varchar(20), --帐套
@find varchar(20) --产业
)AS
declare @chvSql varchar(4000)
declare @qSql1 varchar(50)
declare @qSql2 varchar(50)
declare @qSql3 varchar(50)
if(len(@qSql)>0) --组合查询条件
begin
set @qSql1='and b.productname like ''%'+ @qSql + '%'''
set @qSql2='and a.fproductname like ''%'+ @qSql + '%'''
set @qSql3='and b.fproductname like ''%'+ @qSql + '%'''
end
else
begin
set @qSql1=''
set @qSql2=''
set @qSql3=''
end
set @chvSql ='
SELECT FPRODUCT,FPRODUCTNAME,SUM(FSUM) AS FACVAL, 0 as FADJVAL,'''' as FADJWHYS FROM ( '
+
' select b.fproduct,b.productname as fproductname,sum(b.fnum) as fsum from outorder_list_static a left join
outorder_de_static b on a.fid=b.fid inner join zt' + @fzt + 'sysinfo.DBO.PRODUCT_LIST c on c.fid=b.fproduct' +
'and c.xed=1 and c.fend=1 and c.fcode like ''%' + @fpcode + ' ' + @qSql1 + ' where a.xed=1 and a.findustry
= ' + @find + ' and a.fdept= ' + @fdept + ' and a.fcustomer= ' + @fcustomer + ' and a.etime between ' + @finvTime+ ' and getdate()
group by b.fproduct,b.productname
union all
select a.fproduct,a.fproductname,sum(-a.fqty) as fsum from tblsales a inner join zt ' + @fzt + ' sysinfo.DBO.PRODUCT_LIST
c on c.fid=a.fproduct and c.xed=1 and c.fend=1 and c.fcode like ''% ' + @fpcode + ' ''' + @qSql2 +
' where a.xed=1 and a.fbrand=''XXX'' and a.fdept= ' + @fdept + ' and a.fet= ' + @fet +
'and a.fdate between ' + @fInvTime + ' and getdate() group by a.fproduct,a.fproductname
union all
SELECT a.fproduct,a.fproductname,a.finvsum as fsum FROM InvCheck_Static a inner join zt ' + @fzt + ' sysinfo.DBO.PRODUCT_LIST
c on c.fid=a.fproduct and c.xed=1 and c.fend=1 and c.fcode like ''% ' + @fpcode + ' ''' + @qSql2 + ' where
a.XED=1 AND a.FMARKET= ' + @fet + ' AND fopmon=(datepart(month,getdate())-1)
union all
select b.fproduct,b.fproductname, sum(b.fadjval) as fsum from invcheck_main a inner join invcheck_detail b
on a.fid=b.forderid and a.xed=1 and a.fet= ' + @fet + ' and a.finvtime between ' + @finvTime + ' and
convert(varchar,datepart(year,getdate()))+''-''+convert(varchar,datepart(month,getdate()))+''-01''
inner join zt ' + @fzt + ' sysinfo.DBO.PRODUCT_LIST c on c.fid=b.fproduct and c.xed=1
and c.fend=1 and c.fcode like ''% ' + @fpcode + ' ''' + @qSql3 + ' group by b.fproduct,b.fproductname
) aaa group by fproduct,fproductname' print @chvSql
exec( @chvSql )
RETURN 小弟在vs里调试出@chvSql被赋值始终是'
SELECT FPRODUCT,FPRODUCTNAME,SUM(FSUM) AS FACVAL, 0 as FADJVAL,'' as FADJWHYS FROM ( select b.fproduct,b.productname as fproductname,sum(b.fnum) as fsum from outorder_list_static a left join
outorder_de_static b on a.fid=b.fid inner join zt1s'只有这一小段,后面的不知道为什么总是连接不上
declare @fdept varchar(20)
declare @fcustomer varchar(20)
declare @fet varchar(20)
declare @finvTime varchar(20) --盘点日期
declare @fpcode varchar(20) --产品大类编码
declare @qSql varchar(50) --查询条件
declare @fzt varchar(20) --帐套
declare @find varchar(20) --产业 set @invChkMainFid ='1'
set @fdept ='464'
set @fcustomer ='2306'
set @fet ='17051'
set @finvTime ='1997-04-12' --盘点日期
set @fpcode ='1' --产品大类编码
set @qSql='21' --查询条件
set @fzt ='1' --帐套
set @find ='1' --产业 declare @chvSql varchar(8000)
declare @qSql1 varchar(50)
declare @qSql2 varchar(50)
declare @qSql3 varchar(50)
if(len(@qSql)>0) --组合查询条件
begin
set @qSql1='and b.productname like ''%'+ @qSql + '%'''
set @qSql2='and a.fproductname like ''%'+ @qSql + '%'''
set @qSql3='and b.fproductname like ''%'+ @qSql + '%'''
end
else
begin
set @qSql1=''
set @qSql2=''
set @qSql3=''
end
set @chvSql ='
SELECT FPRODUCT,FPRODUCTNAME,SUM(FSUM) AS FACVAL, 0 as FADJVAL,'''' as FADJWHYS FROM ( '
+
' select b.fproduct,b.productname as fproductname,sum(b.fnum) as fsum from outorder_list_static a left join
outorder_de_static b on a.fid=b.fid inner join zt' + @fzt + 'sysinfo.DBO.PRODUCT_LIST c on c.fid=b.fproduct' +
'and c.xed=1 and c.fend=1 and c.fcode like ''%' + @fpcode + ' ' + @qSql1 + ' where a.xed=1 and a.findustry
= ' + @find + ' and a.fdept= ' + @fdept + ' and a.fcustomer= ' + @fcustomer + ' and a.etime between ' + @finvTime+ ' and getdate()
group by b.fproduct,b.productname
union all
select a.fproduct,a.fproductname,sum(-a.fqty) as fsum from tblsales a inner join zt ' + @fzt + ' sysinfo.DBO.PRODUCT_LIST
c on c.fid=a.fproduct and c.xed=1 and c.fend=1 and c.fcode like ''% ' + @fpcode + ' ''' + @qSql2 +
' where a.xed=1 and a.fbrand=''XXX'' and a.fdept= ' + @fdept + ' and a.fet= ' + @fet +
'and a.fdate between ' + @fInvTime + ' and getdate() group by a.fproduct,a.fproductname
union all
SELECT a.fproduct,a.fproductname,a.finvsum as fsum FROM InvCheck_Static a inner join zt ' + @fzt + ' sysinfo.DBO.PRODUCT_LIST
c on c.fid=a.fproduct and c.xed=1 and c.fend=1 and c.fcode like ''% ' + @fpcode + ' ''' + @qSql2 + ' where
a.XED=1 AND a.FMARKET= ' + @fet + ' AND fopmon=(datepart(month,getdate())-1)
union all
select b.fproduct,b.fproductname, sum(b.fadjval) as fsum from invcheck_main a inner join invcheck_detail b
on a.fid=b.forderid and a.xed=1 and a.fet= ' + @fet + ' and a.finvtime between ' + @finvTime + ' and
convert(varchar,datepart(year,getdate()))+''-''+convert(varchar,datepart(month,getdate()))+''-01''
inner join zt ' + @fzt + ' sysinfo.DBO.PRODUCT_LIST c on c.fid=b.fproduct and c.xed=1
and c.fend=1 and c.fcode like ''% ' + @fpcode + ' ''' + @qSql3 + ' group by b.fproduct,b.fproductname
) aaa group by fproduct,fproductname'
print @chvSql 不过是在查询分析器里面!
但是连出的SQL语句有好几处错误
-------同意
感谢大家 ,终于调出来了ALTER PROCEDURE dbo.InvSum_Prc (
@invChkMainFid varchar(20) ,
@fdept varchar(20),
@fcustomer varchar(20),
@fet varchar(20),
@finvTime varchar(30), --盘点日期
@fpcode varchar(20), --产品大类编码
@qSql varchar(50), --查询条件
@fzt varchar(20), --帐套
@find varchar(20) --产业
)AS declare @chvSql1 varchar(2000) --新增时的Sql
declare @chvSql2 varchar(2000) --修改时的Sql
declare @innerSql1 varchar(200)
declare @innerSql2 varchar(200)
declare @qSql1 varchar(50)
declare @qSql2 varchar(50)
declare @qSql3 varchar(50)
if(len(@fpcode)>0) --组合产品大类查询条件
begin
set @innerSql1=' inner join zt' + @fzt + 'sysinfo.DBO.PRODUCT_LIST c on c.fid=b.fproduct and c.xed=1
and c.fend=1 and c.fcode like ''%' + @fpcode + ''' '
set @innerSql2= ' inner join zt' + @fzt + 'sysinfo.DBO.PRODUCT_LIST c on c.fid=a.fproduct and c.xed=1
and c.fend=1 and c.fcode like ''%' + @fpcode + ''' '
end
else
begin
set @innerSql1=''
set @innerSql2=''
end
if(len(@qSql)>0) --组合产品名称查询条件
begin
set @qSql1='and b.productname like ''%'+ @qSql + '%'''
set @qSql2='and a.fproductname like ''%'+ @qSql + '%'''
set @qSql3='and b.fproductname like ''%'+ @qSql + '%'''
end
else
begin
set @qSql1=''
set @qSql2=''
set @qSql3=''
end
set @chvSql1 ='
SELECT FPRODUCT,FPRODUCTNAME,SUM(FSUM) AS FACVAL, 0 as FADJVAL,'''' as FADJWHYS FROM ( '
+
' select b.fproduct,b.productname as fproductname,sum(b.fnum) as fsum from outorder_list_static a left join
outorder_de_static b on a.fid=b.fid '+ @innerSql1 + 'where 1=1 ' + @qSql1 + ' and a.xed=1 and a.findustry
= ' + @find + ' and a.fdept= ' + @fdept + ' and a.fcustomer= ' + @fcustomer + ' and a.etime between ''' + @finvTime+ ''' and getdate()
group by b.fproduct,b.productname
union all
select a.fproduct,a.fproductname,sum(-a.fqty) as fsum from tblsales a ' + @innerSql2+ ' where 1=1 ' + @qSql2 + ' and a.xed=1 and a.fbrand=''XXX''
and a.fdept= ' + @fdept + ' and a.fet= ' + @fet +
' and a.fdate between ''' + @fInvTime + ''' and getdate() group by a.fproduct,a.fproductname
union all
SELECT a.fproduct,a.fproductname,a.finvsum as fsum FROM InvCheck_Static a ' + @innerSql2 + ' where 1=1 ' + @qSql2 + ' and a.XED=1
AND a.FMARKET= ' + @fet + ' AND fopmon=(datepart(month,getdate())-1)
) aaa group by fproduct,fproductname'
set @chvSql2= '
SELECT FPRODUCT,FPRODUCTNAME,SUM(FSUM) AS FACVAL,0 as FADJVAL,'''' as FADJWHYS FROM ( '
+
' select b.fproduct,b.productname as fproductname,sum(b.fnum) as fsum from outorder_list_static a left join
outorder_de_static b on a.fid=b.fid '+ @innerSql1 + 'where 1=1 ' + @qSql1 + ' and a.xed=1 and a.findustry
= ' + @find + ' and a.fdept= ' + @fdept + ' and a.fcustomer= ' + @fcustomer + ' and a.etime between ''' + @finvTime+ ''' and getdate()
group by b.fproduct,b.productname
union all
select a.fproduct,a.fproductname,sum(-a.fqty) as fsum from tblsales a ' + @innerSql2+ ' where 1=1 ' + @qSql2 + ' and a.xed=1 and a.fbrand=''XXX''
and a.fdept= ' + @fdept + ' and a.fet= ' + @fet +
' and a.fdate between ''' + @fInvTime + ''' and getdate() group by a.fproduct,a.fproductname
union all
select b.fproduct,b.fproductname, sum(b.facval) as fsum from invcheck_main a inner join invcheck_detail b
on a.fid=b.forderid and a.xed=1 and a.fet= ' + @fet + ' and a.fid=' + @invChkMainFid
+ @innerSql1 + 'where 1=1 ' + @qSql3 + ' group by b.fproduct,b.fproductname
) aaa group by fproduct,fproductname'
if(len(@invChkMainFid)>0) -- 修改状态下的SQL
begin
print @chvSql2
--exec( @chvSql2 )
end
else -- 新增状态下的SQL
begin
print @chvSql1
--exec( @chvSql1)
end
RETURN