Passing a variable to a stored procedure in a Select statement RSS

• • Reply
  

  Mongol648

  Member

  170 Points

  255 Posts

  Passing a variable to a stored procedure in a Select statement

  Jan 30, 2013 04:52 PM|LINK

  I have the following SQL query that is using a stored procedure combined with a select statement.  I need to make one of the parameters I pass to the SP a variable (dropdownlist on the page).  How can I accomplish that?

   

  select tab1.station_id, tab1.drug_display_name, tab1.cabinet_dispense_avg, tab1.par_level,
  tab1.pocket_inv, tab1.drug_max_inv, sum(a.trans_qty) as Usage, tab1.rec_par_level, tab1.rec_max_level,
  max(a.trans_qty) as MaxDisp
  from openrowset ('SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=YES;', 'set fmtonly off exec crx_data.dbo.sp_Rpt_Cab_Optimization
  @site_id = null,
  	@station_id = ''T2'', ***I need the variable to be this parameter***
  	@min_avg_days = 1,
  	@max_avg_days = 3,
  	@min_peak_days = 0,
  	@max_peak_days = 0,
  	@max_par_window = 25,
  	@from_dea = 0,
  	@to_dea = 5,
  	@med_class = null,
  	@product = null') as tab1,
  ahi_cab_event as a
  where tab1.drug_dose_id = a.drug_dose_id
  and tab1.station_id = a.station_id
  and a.event_type = 'Dispense'
  and convert(varchar,a.event_dttm) >= convert(varchar,getdate()-7)
  group by
  tab1.station_id, tab1.drug_display_name, tab1.cabinet_dispense_avg, tab1.par_level,
  tab1.pocket_inv, tab1.drug_max_inv, tab1.rec_par_level, tab1.rec_max_level

• • Reply
  

  TabAlleman

  All-Star

  15575 Points

  2702 Posts

  Re: Passing a variable to a stored procedure in a Select statement

  Jan 30, 2013 05:56 PM|LINK

  select tab1.station_id, tab1.drug_display_name, tab1.cabinet_dispense_avg, tab1.par_level,
  tab1.pocket_inv, tab1.drug_max_inv, sum(a.trans_qty) as Usage, tab1.rec_par_level, tab1.rec_max_level,
  max(a.trans_qty) as MaxDisp
  from openrowset ('SQLOLEDB', 'Server=(local);TRUSTED_CONNECTION=YES;', 'set fmtonly off exec crx_data.dbo.sp_Rpt_Cab_Optimization
  @site_id = null,
          @station_id = ''' + @MyVariable + ''',
          @min_avg_days = 1,
          @max_avg_days = 3,
          @min_peak_days = 0,
          @max_peak_days = 0,
          @max_par_window = 25,
          @from_dea = 0,
          @to_dea = 5,
          @med_class = null,
          @product = null') as tab1,
  ahi_cab_event as a
  where tab1.drug_dose_id = a.drug_dose_id
  and tab1.station_id = a.station_id
  and a.event_type = 'Dispense'
  and convert(varchar,a.event_dttm) >= convert(varchar,getdate()-7)
  group by
  tab1.station_id, tab1.drug_display_name, tab1.cabinet_dispense_avg, tab1.par_level,
  tab1.pocket_inv, tab1.drug_max_inv, tab1.rec_par_level, tab1.rec_max_level

  -Tab Alleman
• • Reply
  

  Mongol648

  Member

  170 Points

  255 Posts

  Re: Passing a variable to a stored procedure in a Select statement

  Jan 30, 2013 06:05 PM|LINK

  I am using @unit as my variable.  I get an error saying incorrect syntax near @unit

• • Reply
  

  TabAlleman

  All-Star

  15575 Points

  2702 Posts

  Re: Passing a variable to a stored procedure in a Select statement

  Jan 30, 2013 06:43 PM|LINK

  Sorry, turns out you can't use variables in OpenRowSet.   You have to make the whole query with dynamic sql.   Like:

  SET @sql = '
  select tab1.station_id, tab1.drug_display_name, tab1.cabinet_dispense_avg, tab1.par_level,
  tab1.pocket_inv, tab1.drug_max_inv, sum(a.trans_qty) as Usage, tab1.rec_par_level, tab1.rec_max_level,
  max(a.trans_qty) as MaxDisp
  from openrowset (''SQLOLEDB'', ''Server=(local);TRUSTED_CONNECTION=YES;'', ''set fmtonly off exec crx_data.dbo.sp_Rpt_Cab_Optimization
  @site_id = null,
          @station_id = ''' + @unit + ''',
          @min_avg_days = 1,
          @max_avg_days = 3,
          @min_peak_days = 0,
          @max_peak_days = 0,
          @max_par_window = 25,
          @from_dea = 0,
          @to_dea = 5,
          @med_class = null,
          @product = null'') as tab1,
  ahi_cab_event as a
  where tab1.drug_dose_id = a.drug_dose_id
  and tab1.station_id = a.station_id
  and a.event_type = ''Dispense''
  and convert(varchar,a.event_dttm) >= convert(varchar,getdate()-7)
  group by
  tab1.station_id, tab1.drug_display_name, tab1.cabinet_dispense_avg, tab1.par_level,
  tab1.pocket_inv, tab1.drug_max_inv, tab1.rec_par_level, tab1.rec_max_level
  ';
  
  EXEC (@sql);

   

  http://www.sqlservercentral.com/Forums/Topic496703-8-1.aspx

   

  -Tab Alleman
  • Edited by TabAlleman on Jan 30, 2013 06:43 PM
  • Edited by TabAlleman on Jan 30, 2013 06:44 PM
  • Marked as answer by Chen Yu - MSFT on Feb 06, 2013 06:36 AM