sql - Pass Database Name as a parameter to an Inline Function -


i trying make inline function can used in few dozen stored procedures rather placing code in every single one. function can called various databases need able pass database name parameter. in case, parameter @dbname. here code:

create function [dbo].[fn_units]      (       @inreportcontrolid int,       @dbname varchar(30)      )  returns table  return   (        prep (   select *     (   select rtrim(cp.label) + 'reportunit' type, rtrim(cu.label) value, rc.reportcontrolid, curr.symbol, curp.prefix, curp.factor     @dbname.corpconventionunit ccu inner join                          @dbname.corpunit cu on ccu.nunitid = cu.corpunitid inner join                          @dbname.corpproduct cp on ccu.corpproductid = cp.corpproductid inner join                          phdreports.phdrpt.reportcontrol rc on ccu.corpconventionid = rc.conventionid inner join                          @dbname.corpunit cu2 on cu2.corpunitid = cu.corpbaseunitid inner join                          @dbname.corpcurrency curr on rc.currencyid = curr.corpcurrencyid inner join                          @dbname.corpcurrencyprefix curp on rc.currencyprefix = curp.prefix      union   select rtrim(cp.label) + 'baseunit' type, rtrim(cu2.label) value, rc.reportcontrolid, curr.symbol, curp.prefix, curp.factor     @dbname.corpconventionunit ccu inner join                          @dbname.corpunit cu on  ccu.nunitid = cu.corpunitid inner join                          @dbname.corpproduct cp on ccu.corpproductid = cp.corpproductid inner join                          phdreports.phdrpt.reportcontrol rc on ccu.corpconventionid = rc.conventionid inner join                         @dbname.corpunit cu2 on cu2.corpunitid = cu.corpbaseunitid inner join                          @dbname.corpcurrency curr on rc.currencyid = curr.corpcurrencyid inner join                          @dbname.corpcurrencyprefix curp on rc.currencyprefix = curp.prefix    union    select rtrim(cp.label) + 'reportunitdenom' type, rtrim(cu.label) value, rc.reportcontrolid, curr.symbol, curp.prefix, curp.factor     @dbname.corpconventionunit ccu inner join                          @dbname.corpunit cu on ccu.dunitid = cu.corpunitid inner join                          @dbname.corpproduct cp on ccu.corpproductid = cp.corpproductid inner join                          phdreports.phdrpt.reportcontrol rc on ccu.corpconventionid = rc.conventionid inner join                          @dbname.corpunit cu2 on cu2.corpunitid = cu.corpbaseunitid inner join                          @dbname.corpcurrency curr on rc.currencyid = curr.corpcurrencyid inner join                          @dbname.corpcurrencyprefix curp on rc.currencyprefix = curp.prefix    )as hi   group  hi.reportcontrolid ,value, type, hi.symbol, hi.prefix, hi.factor  )      select reportcontrolid,symbol,prefix, factor,      min(case type when 'oilbaseunit' value end) oilbaseunit,      min(case type when 'nglbaseunit' value end) nglbaseunit,      min(case type when 'gasbaseunit' value end) gasbaseunit,      min(case type when 'oilreportunit' value end) oilreportunit,      min(case type when 'nglreportunit' value end) nglreportunit,      min(case type when 'gasreportunit' value end) gasreportunit,      min(case type when 'gorreportunit' value end) gorreportunit,      min(case type when 'gorreportunitdenom' value end) gorreportunitdenom,      min(case type when 'yieldreportunit' value end) yieldreportunit,      min(case type when 'yieldreportunitdenom' value end) yieldreportunitdenom,      min(case type when 'condensatebaseunit' value end) condensatebaseunit,      min(case type when 'condensatereportunit' value end) condensatereportunit    prep    reportcontrolid = @inreportcontrolid    group reportcontrolid, symbol,prefix, factor 

normally if in stored procedure, store query in varchar , use replace command replace @dbname database being used @ time. doesn't seem work in inline function. figured pass in dbname parameter function, not let me save function because doesn't recognize "@dbname" valid database.

how can replace variable parameter passed in?


Comments

Popular posts from this blog

javascript - how to protect a flash video from refresh? -

android - Associate same looper with different threads -

visual studio 2010 - Connect to informix database windows form application -