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
Post a Comment