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

Tab Alleman · Accepted Answer · 2015-05-28 13:12:08Z

6

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);

edited May 28, 2015 at 13:12

answered May 27, 2015 at 12:46

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use Parameter In OPENROWSET Sql Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related