在OPENROWSET Sql Server中使用参数 [英] Use Parameter In OPENROWSET Sql Server

问题描述

我正在创建一个存储过程，我在其中调用另一个存储过程（这个过程返回很多列，我只想要一个列值，所以我不能创建临时表来存储值）使用OPENROWSET。 />


当我使用以下时，它没关系

声明@AgencyID int = 15，@ PatientID int = 3701

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;

工作正常。但是我想传递参数来调用USP_Billing_GetPatientWithInsurence，因为值是动态的。所以我使用以下代码

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

推荐答案

将select语句创建为字符串变量并执行字符串。不要忘记通过添加char（39）或使用多个引号在字符串变量中添加引号。

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.

这篇关于在OPENROWSET Sql Server中使用参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！