Use Parameter In OPENROWSET Sql Server

Question

I am creating an stored procedure in which I am calling an another stored procedure(This procedure is returned lot of columns and I want only one column value So I can't create temp table to store values) using OPENROWSET.

When I am use following then it's alright

declare @AgencyID int=15,@PatientID int=3701
SELECT a.PrimaryInsuredName 
    FROM OPENROWSET('SQLNCLI',  
    'Server=ServerName;Database=DbName;Trusted_Connection=yes',
    'exec USP_Billing_GetPatientWithInsurence 3701,15') AS a;

It's working fine. But I want to pass parameters for calling USP_Billing_GetPatientWithInsurence because values will be dynamic. So I use following code

 declare @AgencyID int=15,@PatientID int=3701
    SELECT a.PrimaryInsuredName 
        FROM OPENROWSET('SQLNCLI',  
        'Server=ServerName;Database=DbName;Trusted_Connection=yes',
        'exec USP_Billing_GetPatientWithInsurence '+ @PatientID +','+ @AgencyID+'') AS a;

But it's not working When I run this query then an error occurred Incorrect syntax near '+'. I don't know why this is coming. Please provide a solution to this. I googled also for this but can't found a proper solution.

Thanks

Answer 1

You have to make your entire SELECT string dynamic:

 declare @AgencyID int=15,@PatientID int=3701
DECLARE @SQLStr varchar(max)='
    SELECT a.PrimaryInsuredName 
        FROM OPENROWSET(''SQLNCLI'',  
        ''Server=ServerName;Database=DbName;Trusted_Connection=yes'',
        ''exec USP_Billing_GetPatientWithInsurence '+ CAST(@PatientID AS varchar(15)) +','+ CAST(@AgencyID AS varchar(15)) +''') AS a';
EXECUTE(@SQLStr);