How to add a variable in the open query in a stored procedure

Question

I have an stored procedure that receives a parameter of store id. I need that variable to be added in a openquery tsql script I am not able to do it.. Look in my code where it says @var thats where the variable will need to work

I tried to reference to this https://support.microsoft.com/en-us/help/314520/how-to-pass-a-variable-to-a-linked-server-query but I cant get it to work

SELECT x.*
FROM (
    SELECT tl.title_log_sts_cd
        ,tl.ro_vin
        ,tl.ro_store_id
        ,ll.CurrentLoanStatus
        ,bc.vin
        ,bc.BorrowerId
        ,ll.DisplayLoanNumber
        ,CONVERT(DATE, CONVERT(VARCHAR(10), ll.CreateDateKey, 7)) LoanDate
        ,CONCAT (
            bb.LastName
            ,','
            ,bb.FirstName
            ) CustomerName
        ,ll.CurrentPrincipalBalanceAmt + ll.CurrentFeeBalanceAmt TotalDue
    FROM OPENQUERY(TLXPRD, 'SELECT * FROM TITLE_LOG 
 WHERE title_log_sts_cd in (''Ready to Send to DMV'', ''Sent to DMV'') 
 and ro_store_id =@VAR ') AS tl
    JOIN EIS.Borrower.Collateral bc WITH (NOLOCK) ON tl.ro_vin = bc.Vin
    LEFT JOIN eis.loan.loan ll WITH (NOLOCK) ON ll.BorrowerId = bc.BorrowerId
        AND convert(DATE, tl.created_ts) = CONVERT(DATE, CONVERT(VARCHAR(10), ll.CreateDateKey, 7))
    LEFT JOIN EIS.Borrower.Borrower BB ON bb.borrowerid = ll.BorrowerId
    ) AS x
ORDER BY vin

Expected get some data from oracle join it in with tsql

Answer 1

I didn't totally get what you are trying to do but I hope this is what you are looking for.

 FROM OPENQUERY(TLXPRD, concat( 'SELECT * FROM TITLE_LOG 
 WHERE title_log_sts_cd in (''Ready to Send to DMV'', ''Sent to DMV'') 
 and ro_store_id =', cast(@VAR as nvarchar(50) ) ))

basically, you had your variable in your string but you want to have it as a value. so we get the value and put it in your string.