title: Select from @localvariable
body: <p>I have a table of my server path, and I have a stored procedure where I need to call my server path base on the platform. I don't know why it's not working. Below is what I have so far.</p>
  <pre><code>SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  ALTER PROCEDURE [POLSAPSU].[SelectOrdersPOLDate] (@FROM DATETIME, @TO DATETIME)
  AS
  
  DECLARE @serverpath varchar(255)
  DECLARE @query varchar(max)
  
  BEGIN
  
  SET @serverpath = (SELECT [path] from [param] where [platform] = 'POL')
  
  SET @query = '
  
  select 'POL' + '0'+ordh_sysrefno as ZINDEX,ordh_conttp AS POL_ORD_PKG_TYPE, ordh_pckgrefno AS POL_PKG_REFNO, '''' AS POL_PKG_PRODN, ''1101'' AS SALES_ORG, ''10'' AS DISTR_CHAN, ''11'' AS DIVISION, '''' AS POL_RTV_L_N, '''' AS CT_VALID_F, '''' AS CT_VALID_T, ordh_docno AS PURCH_NO_C 
  from ' + @serverpath +'.ord_hdr A''
  where cast(ordh_createdate as date) BETWEEN @FROM AND @TO 
      and Exists(select * from ' + @serverpath +'.ord_dtl B where B.ordd_sysrefno = ordh_sysrefno)
  union
  select ''POL''+pkgh_production+ pkgh_refno, pkgh_type, '''', pkgh_production, ''1101'', ''10'', ''11'', '',convert(varchar(8),pkgh_effst,112), convert(varchar(8),pkgh_effend,112), pkgh_docno 
  from  ' + @serverpath + '.pckg_hdr where cast(pkgh_createdate as date) BETWEEN @FROM AND @TO'
  
  EXEC (@query)
  
  END
  </code></pre>
  <p>I'm getting this error:</p>
  <blockquote>
  <p>Incorrect syntax near '
  where cast(ordh_createdate as date) BETWEEN @FROM AND @TO
  and Exists(select * from POLTESTSERVER.POL.sysadm.ord_dtl B where '.</p>
  </blockquote>
  <p>I am also considering this kind of approach</p>
  <pre><code>Select * from (select serverpath where platform = pol)
  </code></pre>
  <p>but I need to add the table name after the select statement in from clause and I don't have idea how. I am using SQL Server 2019.</p>
  <p>Thank you.</p>