Select from @localvariable-CodePudding

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.

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

I'm getting this error:

Incorrect syntax near ' where cast(ordh_createdate as date) BETWEEN @FROM AND @TO and Exists(select * from POLTESTSERVER.POL.sysadm.ord_dtl B where '.

I am also considering this kind of approach

Select * from (select serverpath where platform = pol)

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.

Thank you.

CodePudding user response：

I found multiple issues here.

Date parameters inside the sql query string are not properly escaped.

Use concat() function instead of adding those constants and columns

Columns with empty values are not property escaped.

Try below updated script.

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 concat(''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 '''  cast(@FROM as varchar(30))  ''' AND '''  cast(@TO as varchar(30))   '''
    and Exists(select * from '   @serverpath  '.ord_dtl B where B.ordd_sysrefno = ordh_sysrefno)
union
select concat(''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 '''  cast(@FROM as varchar(30))  ''' AND '''  cast(@TO as varchar(30))   ''''


EXEC (@query)

END