question

siera_gld avatar image
siera_gld asked

Pass Parameters to Dynamic Sql

I need to pass date parameters to an openquery - so that I am not pulling more information from a linked server than necessary. And ultimately I will update a sales amount in a temp table from the dynamic sql Any ideas how to pass dates to oracle? declare @beg_dt datetime declare @end_dt datetime set @beg_dt = '2011-12-01' set @end_dt = '2011-12-31' update #temp set sales_qty = ns.sales from ( SELECT ns.sales ,ns.sale_date ,ns.CNTRC_ID ,ns.CUST_ACCT_ID ,ns.EM_ITEM_NUM ,lead.TYP_ID FROM OPENQUERY (ORACLE_DB, 'SELECT SUM(SLS_QTY)as sales, TO_CHAR((SLS_PROC_WRK_DT), ''YYYYMM'') AS SALE_DATE, CNTRC_ID, CUST_ACCT_ID, ITEM_NUM FROM ORCL.V_SALE_ITEM where rownum oracleparametersdynamic-sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
You cannot pass parameters to OPENQUERY. The only way to parametrize the OPENQUERY is to construct a really dynamic sql and execute it. Here is link to [MSDN on OPENQUERY][1]. As you can read there, OPENQUERY even doesn't accept variables for arguments therefore you cannot pass parameters. something like this: declare @beg_dt datetime declare @end_dt datetime DECLARE @sql nvarchar(max) set @beg_dt = '2011-12-01' set @end_dt = '2011-12-31' SET @sql = 'update #temp set sales_qty = ns.sales from ( SELECT ns.sales ,ns.sale_date ,ns.CNTRC_ID ,ns.CUST_ACCT_ID ,ns.EM_ITEM_NUM ,lead.TYP_ID FROM OPENQUERY (ORACLE_DB, ''SELECT SUM(SLS_QTY)as sales, TO_CHAR((SLS_PROC_WRK_DT), ''YYYYMM'') AS SALE_DATE, CNTRC_ID, CUST_ACCT_ID, ITEM_NUM FROM ORCL.V_SALE_ITEM where SLS_PROC_WRK_DT BETWEEN ''''' + CONVERT(nvarchar(8), @beg_dt, 112) + ''''' AND ''''' + CONVERT(nvarchar(8), @beg_dt, 112) + ''''' GROUP BY SLS_PROC_WRK_DT , TO_CHAR((SLS_PROC_WRK_DT), ''YYYYMM'') , CNTRC_ID, CUST_ACCT_ID, EM_ITEM_NUM'') as ns JOIN .dbo.T_PRC_XREF lead ON ns.CNTRC_ID = lead.LEAD WHERE lead.STAT_CD = ''C'' GROUP BY sales, SALE_DATE, CNTRC_ID, CUST_ACCT_ID, lead.TYP_ID, ns.ITEM_NUM' EXEC (@sql) If you have declared the #temp temporary table prior executing the dynamic sql, it will be available also inside the dynamic sql. [1]: http://msdn.microsoft.com/en-us/library/ms188427.aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.