我们经常要拼SQL语句，往往又想将拼成的SQL语句返回值到语句外面用。这时候，就需要用到SQL Server中自带的SP_EXECUTESQL了：
--
-If Over Bill Case----------------------------------------------------------------------------------------------------------
declare
@strInVoicetable
nvarchar
(
1000
),
@strOrdertable
nvarchar
(
1000
),
@Inv_NO
nvarchar
(
1000
)
set
@Inv_NO
=
'
9010002999
'
set
@strInVoicetable
=
'
fi_invoice_detail_2008
'
set
@strOrdertable
=
'
order_detail_2008
'
DECLARE
@OverSql
NVARCHAR
(
4000
),
@OverParameter
NVARCHAR
(
1000
)
declare
@IsOverBill
int
Select
@OverSql
=
'
select @iIsOverBill =case when (Convert(numeric(19,2),det.Med_Cost * fim.inv_exch) > Convert(numeric(19,2),(odet.UnitCost + odet.ProductionCost))) or (Convert(numeric(19,2),det.AC1 * fim.inv_exch) > Convert(numeric(19,2),odet.AC1Amount)) then 1 else case when @iIsOverBill = 1 then 1 else 0 endendfrom fi_invoice_master fim left join
'
+
@strInVoicetable
+
'
det on fim.invoice_master_id = det.invoice_master_id left join order_master ma on det.OrderNo=ma.OrderNo and det.OrderVersion=ma.OrderVersion left join
'
+
@strOrdertable
+
'
odet on det.spotcode=odet.spotcode and det.order_detail_id = odet.order_detail_idwhere fim.inv_no in(
'
+
@Inv_NO
+
'
)
'
,
@OverParameter
=
N
'
@iIsOverBill int OUTPUT
'
EXEC
SP_EXECUTESQL
@OverSql
,
@OverParameter
,
@IsOverBill
OUTPUT
select
@IsOverBill
--
----------------------------------------------------------------------------------------------------------------------------
我是数据库笨笨。
转载于:https://www.cnblogs.com/KenBlove/archive/2008/07/08/1238077.html
