Click here to Skip to main content
15,911,531 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
SQL
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
SQL
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
Posted

1 solution

Create the select statement as a string variable and execute the string. Don't forget to add the quotes in string variable by adding char(39) or using multiple quotes.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900