exec sp_executesql N'
select mf_pos.os_no 采购订单号, CONVERT(varchar(30),mf_pos.os_dd,23 ) 单据日期,
isnull(MF_POS.cus_no,'''') as 厂商代号 , isnull(cust.name,'''') as 厂商名称,
ct.name 主供应商,
isnull(MF_POS.PO_DEP,'''') as 部门代号,isnull(dept.name,'''') as 部门名称,
isnull(TF_POS.prd_no,'''') as 货品代号 , isnull(prdt.name,'''') as 货品名称,
isnull(prdt.spc,'''') 规格,BAR_CODE 条形码, isnull(prdt.ut,'''') 单位, isnull(prdt.BAR_CODE,'''') 条形码,
isnull (mf_pos.sal_no,'''') as 业务员代号, salm.name as 业务员名称,
isnull(wh.wh,'''') 仓库代号,isnull(wh.name,'''')仓库名称,
isnull(tf_pos.bat_no,'''') 批号, isnull(tf_pos.qty,0) AS 数量, isnull (tf_pos.qty1,0) AS 副数量,
isnull (tf_pos.up,0) as 单价, isnull(tf_pos.AMT,0) as 金额, isnull(tf_pos.AMTN,0) as 未税本位币,
isnull(tf_pos.tax,0) as 税额, isnull(qty_rk,0) 送检量,
tf_pos.QTY_BAD 不合格数量,tf_pos.QTY_YS 已验收数量 ,
convert(varchar(30),tf_pos.est_dd,23) 预交日期, isnull(tf_pos.QTY_PS,0)已交数量,
isnull(mf_pos.usr,'''') as 制单人,
isnull(mf_pos.chk_man,'''') as 审核人,
isnull ( bil_spc.name ,'''') as 单据类别 ,isnull (bil_spc.NAME,'''') AS 单据名称,
cast(isnull(mf_pos.rem,'''') as varchar(200))备注
from MF_POS
left join tf_pos on mf_pos.os_no= tf_pos.os_no
left join salm on mf_pos.sal_no = salm.sal_no
left join prdt on tf_pos.prd_no = prdt.prd_no
left join cust on mf_pos.cus_no= cust.cus_no
left join cust ct on ct.cus_no= prdt.SUP1
left join my_wh wh on wh.wh = tf_pos.wh
left join dept on mf_pos.po_dep = dept .dep
left join bil_spc on bil_spc.spc_no = mf_pos .BIL_TYPE and bil_spc.bil_ID=''PC''
where MF_pOS.os_id = ''PO''
AND mf_pos.os_dd between @Star and @End
order by CONVERT(varchar(30),mf_pos.os_dd,23 ),mf_pos.os_no
',N'@Star datetime,@End datetime',@Star='2014-01-15 00:00:00',@End='2015-04-15 00:00:00'
如果我想按照图上几个功能做 SQL语句该怎么写。。
我刚刚开始接触实施，这些看着运请问有什么好的入门方法
------解决思路----------------------
貌似是控件要显示的数据需要从DB中读取出来。。。
------解决思路----------------------
都已经是动态语句了，就让前台把 WHERE 拼好了传进来。就一个参数@where，以后前台加条件后台都不用改。
exec sp_executesql N'
select...
left join bil_spc on bil_spc.spc_no = mf_pos .BIL_TYPE and bil_spc.bil_ID=''PC''
'+@where+N'
order by CONVERT(varchar(30),mf_pos.os_dd,23 ),mf_pos.os_no'
------解决思路----------------------
怎么加?
比如加厂商号
exec sp_executesql N'
select mf_pos.os_no 采购订单号, CONVERT(varchar(30),mf_pos.os_dd,23 ) 单据日期,
isnull(MF_POS.cus_no,'''') as 厂商代号 , isnull(cust.name,'''') as 厂商名称,
ct.name 主供应商,
isnull(MF_POS.PO_DEP,'''') as 部门代号,isnull(dept.name,'''') as 部门名称,
isnull(TF_POS.prd_no,'''') as 货品代号 , isnull(prdt.name,'''') as 货品名称,
isnull(prdt.spc,'''') 规格,BAR_CODE 条形码, isnull(prdt.ut,'''') 单位, isnull(prdt.BAR_CODE,'''') 条形码,
isnull (mf_pos.sal_no,'''') as 业务员代号, salm.name as 业务员名称,
isnull(wh.wh,'''') 仓库代号,isnull(wh.name,'''')仓库名称,
isnull(tf_pos.bat_no,'''') 批号, isnull(tf_pos.qty,0) AS 数量, isnull (tf_pos.qty1,0) AS 副数量,
isnull (tf_pos.up,0) as 单价, isnull(tf_pos.AMT,0) as 金额, isnull(tf_pos.AMTN,0) as 未税本位币,
isnull(tf_pos.tax,0) as 税额, isnull(qty_rk,0) 送检量,
tf_pos.QTY_BAD 不合格数量,tf_pos.QTY_YS 已验收数量 ,
convert(varchar(30),tf_pos.est_dd,23) 预交日期, isnull(tf_pos.QTY_PS,0)已交数量,
isnull(mf_pos.usr,'''') as 制单人,
isnull(mf_pos.chk_man,'''') as 审核人,
isnull ( bil_spc.name ,'''') as 单据类别 ,isnull (bil_spc.NAME,'''') AS 单据名称,
cast(isnull(mf_pos.rem,'''') as varchar(200))备注
from MF_POS
left join tf_pos on mf_pos.os_no= tf_pos.os_no
left join salm on mf_pos.sal_no = salm.sal_no
left join prdt on tf_pos.prd_no = prdt.prd_no
left join cust on mf_pos.cus_no= cust.cus_no
left join cust ct on ct.cus_no= prdt.SUP1
left join my_wh wh on wh.wh = tf_pos.wh
left join dept on mf_pos.po_dep = dept .dep
left join bil_spc on bil_spc.spc_no = mf_pos .BIL_TYPE and bil_spc.bil_ID=''PC''
where MF_pOS.os_id = ''PO''
AND mf_pos.os_dd between @Star and @End
AND MF_POS.cus_no = @cus_no
order by CONVERT(varchar(30),mf_pos.os_dd,23 ),mf_pos.os_no
',N'@Star datetime,@End datetime,@cus_no VARCHAR(20)',@Star='2014-01-15 00:00:00',@End='2015-04-15 00:00:00'
,@cus_no='查询号码'