initialise non-static cost stored way ms sql server

i have combined following stored procedure..

create way [dbo].[udsprbhprimbustypestartup]
(
  @code char(5)
  , @desc varchar(255) output
)
as
declare @servername nvarchar(30)
declare @dbase nvarchar(30)
declare @sql nvarchar(2000)

set @servername = 
  convert(nvarchar,
  (select spdata dbspecificdata where splookup = 'cmsserver'))
set @dbase = 
  convert(nvarchar,
  (select spdata dbspecificdata where splookup = 'cmsdbname'))

set @sql = 
  'select clnt_cat_desc ' + @servername + 
  '.' + @dbase + '.dbo.hbl_clnt_cat where dead = ''n''
  clnt_cat_code = ''' + @code + ''''

execute sp_executesql @sql

return

this way used opposite databases opposite servers combined enterprising sql foster maintenance. way also runs opposite server way points to.

i wish cost way value table...

declare @clid bigint
declare @fileid bigint
declare @mycode char(5)
declare @mydesc varchar(255)
declare @@tempdesc varchar(255)

set @clid = 1831400022
set @fileid = 2072551358
set @mycode = 
  (select _clientprimbusinesstype udbhextclient where clid = @clid)

set @mydesc = 
  exec udsprbhprimbustypestartup @code = @mycode, @desc = @@tempdesc output
----------------------------------------------------------------------------
select
  a.clid
  , b.fileid
  , c.usrfullname clientpartner
  , e.usrfullname clientfeeearner
  , @mydesc clientprimarybusinesstype
from 
  dbclient a
    middle join
  dbfile b
    on
  a.clid = b.clid
    middle join
  dbuser c
    
  a.feeusrid = c.usrid
    middle join
  udbhextclient d
    on
  a.clid = d.clid
    middle join
  dbuser e
    on
  d._clientfeeearner = e.usrid
where 
  a.clid = @clid
  b.fileid = @fileid

i know crude syntax, nonetheless i am perplexing grasp but resorting surrogate tables meant maintain opposing 30 opposite servers 3 5 databases each.

smink - attempted your fortitude got following results...