运行一段SQL语句并返回值代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】运行一段SQL语句并返回值
作者:Dezai.CN / 发布于2011/9/16/ 963
<div><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">-If Over Bill Case----------------------------------------------------------------------------------------------------------</span><span style="color: rgb(0,128,128)">
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@strInVoicetable</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">nvarchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">1000</span><span style="color: rgb(0,0,0)">),</span><span style="color: rgb(0,128,0)">@strOrdertable</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">nvarchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">1000</span><span style="color: rgb(0,0,0)">),</span><span style="color: rgb(0,128,0)">@Inv_NO</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">nvarchar</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">1000</span><span style="color: rgb(0,0,0)">)
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@Inv_NO</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">9010002999</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@strInVoicetable</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">fi_invoice_detail_2008</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">set</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@strOrdertable</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">order_detail_2008</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">DECLARE</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@OverSql</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">NVARCHAR</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">4000</span><span style="color: rgb(0,0,0)">),</span><span style="color: rgb(0,128,0)">@OverParameter</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">NVARCHAR</span><span style="color: rgb(0,0,0)">(</span><span style="color: rgb(128,0,0); font-weight: bold">1000</span><span style="color: rgb(0,0,0)">)
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">declare</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@IsOverBill</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,0,255)">int</span><span style="color: rgb(0,0,0)">
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">Select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@OverSql</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />select @iIsOverBill =
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />case when (Convert(numeric(19,2),det.Med_Cost * fim.inv_exch) > Convert(numeric(19,2),(odet.UnitCost + odet.ProductionCost))) 
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /> or (Convert(numeric(19,2),det.AC1 * fim.inv_exch) > Convert(numeric(19,2),odet.AC1Amount))
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /> then 1 
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /> else 
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /> case when @iIsOverBill = 1 then 1 else 0 end
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />end
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />from fi_invoice_master fim 
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />left join </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@strInVoicetable</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> det on fim.invoice_master_id = det.invoice_master_id 
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />left join order_master ma on det.OrderNo=ma.OrderNo and det.OrderVersion=ma.OrderVersion 
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />left join </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@strOrdertable</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)"> odet on det.spotcode=odet.spotcode and det.order_detail_id = odet.order_detail_id
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />where fim.inv_no in(</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@Inv_NO</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">+</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">)
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)">,
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,128,0)">@OverParameter</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(128,128,128)">=</span><span style="color: rgb(0,0,0)"> N</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(255,0,0)">@iIsOverBill int OUTPUT</span><span style="color: rgb(255,0,0)">'</span><span style="color: rgb(0,0,0)"> 
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">EXEC</span><span style="color: rgb(0,0,0)"> SP_EXECUTESQL </span><span style="color: rgb(0,128,0)">@OverSql</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@OverParameter</span><span style="color: rgb(0,0,0)">,</span><span style="color: rgb(0,128,0)">@IsOverBill</span><span style="color: rgb(0,0,0)"> OUTPUT 
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" />
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,0,255)">select</span><span style="color: rgb(0,0,0)"> </span><span style="color: rgb(0,128,0)">@IsOverBill</span><span style="color: rgb(0,0,0)">
<img alt="" align="top" src="http://www.cnblogs.com/Images/OutliningIndicators/None.gif" /></span><span style="color: rgb(0,128,128)">--</span><span style="color: rgb(0,128,128)">----------------------------------------------------------------------------------------------------------------------------</span></div>
试试其它关键字
　返回值　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)