openquery 带参数的方法及exec返回结果到变量中的方法

declare @QTYRECEIVED int
declare @RECEIPTKEY nvarchar(50)
declare @SKU nvarchar(50)
set @RECEIPTKEY='1209010002'
set @SKU='922W1B32BL080'
declare @Sql nvarchar(1000)
SET @Sql = 'SELECT RECEIPTKEY,SKU,QTYRECEIVED FROM WMWHSE1.RECEIPTDETAIL WHERE RECEIPTKEY='''+@RECEIPTKEY+''' AND SKU='''+@SKU+''''
SET @Sql = 'SELECT @QTYRECEIVED=SUM(QTYRECEIVED) FROM OPENQUERY([INFORWMS], ''' + REPLACE(@Sql, '''', '''''') + ''') GROUP BY RECEIPTKEY,SKU'
exec sp_executesql @Sql,N'@QTYRECEIVED int output',@QTYRECEIVED output 
select @QTYRECEIVED

posted @ 2012-09-05 13:20  做IT人  阅读(2909)  评论(0)    收藏  举报