Pass variable to openquery inside Table-Valued Function

Transact-SQL
1

Hi,

Can't seem to find the right way to pass variables to my openquery:

IF OBJECT_ID(N'[dbo].[fn_Tias]', N'TF') IS NOT NULL
DROP FUNCTION [dbo].[fn_Tias];
GO
CREATE FUNCTION [dbo].[fn_Tias]
(
-- Add the parameters for the function here
@NPAPassed char(3),
@NXXPassed char(3),
@OE_StartPassed char(11),
@OE_EndPassed char(11)

)

RETURNS @t TABLE
(
SwitchCLLI char(11),
NPA int,
Frame_NXX int,
Lead_NXX int,
OE_NUM char(11),
DCOSG char(3),
TEG varchar(5),
DIRNUM VARCHAR(12),
PP varchar(25),
SPECIAL_CIR_NUM varchar(50),
[COS] varchar(10),
CABLE varchar(12),
PAIR varchar(12),
CABLE_PAIR_ZONE varchar(5),
AUX_TYPE varchar(50),
AUX_ID varchar(50)
)

AS
BEGIN
DECLARE
@SwitchCLLI char(11),
@NPA int,
@Frame_NXX int,
@Lead_NXX int,
@OE_NUM char(11),
@DCOSG char(3),
@TEG varchar(5),
@DIRNUM VARCHAR(12),
@PP varchar(25),
@SPECIAL_CIR_NUM varchar(50),
@COS varchar(10),
@CABLE varchar(12),
@PAIR varchar(12),
@CABLE_PAIR_ZONE varchar(5),
@AUX_TYPE varchar(50),
@AUX_ID varchar(50)

SELECT top 10 @SwitchCLLI = Merlin.SwitchCLLI, @NPA = t1.NPA, @Frame_NXX = t1.FRAME_NXX, @Lead_NXX = t1.LEAD_NXX, @OE_NUM = t1.OE_NUM, @DCOSG = t1.DCOSG,
@TEG = t1.TEG, @DIRNUM = t1.DIRNUM, @PP = t1.PP, @SPECIAL_CIR_NUM = t1.SPECIAL_CIR_NUM, @COS = t1.[COS], @CABLE = t1.CABLE, @PAIR = t1.PAIR,
@CABLE_PAIR_ZONE = t1.CABLE_PAIR_ZONE, @AUX_TYPE = t1.AUX_TYPE, @AUX_ID = t1.AUX_ID
FROM OPENQUERY(PRPAIRSI,
'Select t1.NPA, t1.FRAME_NXX, t1.LEAD_NXX, t1.OE_NUM, t1.DCOSG, (t1.TEG_TOUCH_CD_W||t1.TEG_TOUCH_CD_E||t1.TEG_ESSENTIAL_CD) as TEG,
(t4.D_NPA||t4.D_NXX||t4.LINE_NUM) as DIRNUM, t4.PARTY_POSITION as PP,t2.SPECIAL_CIR_NUM, t1.COS, t1.FEEDER_CABLE_NUM as CABLE, t1.FEEDER_PAIR_NUM as PAIR,t1.OE_FRAME_MOD_ZONE as CABLE_PAIR_ZONE,
t3.AUX_TYPE, t3.AUX_ID from IPAIRS_IA.TORGEQP t1
LEFT OUTER JOIN IPAIRS_IA.TSPECCIR t2 on t2.NPA = t1.NPA and t2.LEAD_NXX = t1.LEAD_NXX and t2.FEEDER_CABLE_NUM = t1.FEEDER_CABLE_NUM and t2.FEEDER_PAIR_NUM = t1.FEEDER_PAIR_NUM
LEFT OUTER JOIn IPAIRS_IA.TAUXEQPT t3 on t3.NPA = t1.NPA and t3.FRAME_NXX = t1.FRAME_NXX and t3.FEEDER_CABLE_NUM = t1.FEEDER_CABLE_NUM and t3.FEEDER_PAIR_NUM = t1.FEEDER_PAIR_NUM
LEFT OUTER JOIN IPAIRS_IA.TDIRNUM t4 on t4.NPA = t1.NPA and t4.FRAME_NXX = t1.FRAME_NXX and t4.FEEDER_CABLE_NUM = t1.FEEDER_CABLE_NUM and t4.FEEDER_PAIR_NUM = t1.FEEDER_PAIR_NUM
WHERE ((t4.D_NPA||t4.D_NXX||t4.LINE_NUM) is not null Or t2.SPECIAL_CIR_NUM is not null)
and t1.OE_NUm >= ''' + @OE_StartPassed + ''' and t1.OE_NUm <= ''' + @OE_EndPassed + ''' AND t1.NPA = ''' + @NPAPassed + ''' AND t1.LEAD_NXX = ''' + @NXXPassed + '''' )
AS t1 LEFT OUTER JOIN Merlin.merlin.dbo.NPANNXAssignment AS Merlin ON LEFT(Merlin.NPANNXID, 3) = t1.NPA AND RIGHT(Merlin.NPANNXID, 3) = t1.LEAD_NXX

RETURN

END

2

what is this PRPAIRSI?

3

PRPAIRSI is my Oracle Linked Server

4

I think I go it to work as follow, but now I need to add the results to my @t table
Any Idea ?

Select @SQL = 'Select @SwitchCLLI, @NPA, @Frame_NXX, @Lead_NXX, @OE_NUM, @DCOSG,@TEG,@DIRNUM, @PP, @SPECIAL_CIR_NUM,
@COS,@CABLE, @PAIR, @CABLE_PAIR_ZONE,@AUX_TYPE, @AUX_ID from(
SELECT   @SwitchCLLI = Merlin.SwitchCLLI, @NPA = t1.NPA, @Frame_NXX = t1.FRAME_NXX, @Lead_NXX = t1.LEAD_NXX, @OE_NUM = t1.OE_NUM, @DCOSG = t1.DCOSG, 
@TEG = t1.TEG, @DIRNUM = t1.DIRNUM, @PP = t1.PP, @SPECIAL_CIR_NUM = t1.SPECIAL_CIR_NUM, @COS = t1.[COS], @CABLE = t1.CABLE, @PAIR = t1.PAIR, 
                         @CABLE_PAIR_ZONE = t1.CABLE_PAIR_ZONE, @AUX_TYPE = t1.AUX_TYPE, @AUX_ID = t1.AUX_ID
FROM            OPENQUERY(PRPAIRSI, 
                         ''Select t1.NPA, t1.FRAME_NXX, t1.LEAD_NXX, t1.OE_NUM, t1.DCOSG, (t1.TEG_TOUCH_CD_W||t1.TEG_TOUCH_CD_E||t1.TEG_ESSENTIAL_CD) as TEG, 
(t4.D_NPA||t4.D_NXX||t4.LINE_NUM) as DIRNUM, t4.PARTY_POSITION as PP,t2.SPECIAL_CIR_NUM, t1.COS, t1.FEEDER_CABLE_NUM as CABLE, t1.FEEDER_PAIR_NUM as PAIR,t1.OE_FRAME_MOD_ZONE as CABLE_PAIR_ZONE, 
t3.AUX_TYPE, t3.AUX_ID from IPAIRS_IA.TORGEQP t1  
LEFT OUTER JOIN IPAIRS_IA.TSPECCIR t2 on t2.NPA = t1.NPA and t2.LEAD_NXX = t1.LEAD_NXX and t2.FEEDER_CABLE_NUM = t1.FEEDER_CABLE_NUM and t2.FEEDER_PAIR_NUM = t1.FEEDER_PAIR_NUM
LEFT OUTER JOIn IPAIRS_IA.TAUXEQPT t3 on t3.NPA = t1.NPA and t3.FRAME_NXX = t1.FRAME_NXX and t3.FEEDER_CABLE_NUM = t1.FEEDER_CABLE_NUM and t3.FEEDER_PAIR_NUM = t1.FEEDER_PAIR_NUM
LEFT OUTER JOIN IPAIRS_IA.TDIRNUM t4 on t4.NPA = t1.NPA and t4.FRAME_NXX = t1.FRAME_NXX and t4.FEEDER_CABLE_NUM = t1.FEEDER_CABLE_NUM and t4.FEEDER_PAIR_NUM = t1.FEEDER_PAIR_NUM
WHERE ((t4.D_NPA||t4.D_NXX||t4.LINE_NUM) is not null Or t2.SPECIAL_CIR_NUM is not null)
and  t1.OE_NUm >=  ''''' + @OE_StartPassed + ''''' and t1.OE_NUm <=  ''''' + @OE_EndPassed + ''''' AND t1.NPA like ''''' + NPAPassed + ''''' AND t1.LEAD_NXX like ''''' + @NXXPassed + ''''' )
AS t1 LEFT OUTER JOIN Merlin.merlin.dbo.NPANNXAssignment AS Merlin ON LEFT(Merlin.NPANNXID, 3) = t1.NPA AND RIGHT(Merlin.NPANNXID, 3) = t1.LEAD_NXX) as t2'
5

Is it 100% impossible for any of those parametrs to contain a single-quote?

If it was me I would put a REPLACE on each of them - just belt-and-braces

...
WHERE ((t4.D_NPA||t4.D_NXX||t4.LINE_NUM) is not null Or t2.SPECIAL_CIR_NUM is not null)
and t1.OE_NUm >= ''' 
    + REPLACE(@OE_StartPassed, '''', '''''')
    + ''' and t1.OE_NUm <= '''
    + REPLACE(@OE_EndPassed, '''', '''''')
    + ''' AND t1.NPA 
...