调用存储过程并检查返回值

I have a stored procedure called pat_selectPatientById and that stored procedure returns a true or false using ISNULL(@isEqual, 0) as IsProviderSameAsPCP.

我有一个名为pat_selectPatientById的存储过程，该存储过程使用ISNULL(@isEqual, 0)作为IsProviderSameAsPCP返回一个true或false。

I am trying to call this stored procedure using a C# method by calling Application.WebService.ExecuteQuery("pat_selectPatientById"). But I'm not having any luck - can someone point me in the right direction?

我试图通过调用Application.WebService.ExecuteQuery(“pat_selectPatientById”)使用c#方法调用这个存储过程。但我运气不好——谁能给我指出正确的方向吗?

Thanks a lot guys

非常感谢人

Code:

代码:

declare @isEqual bit = 
    (select 
        top 1 1 as IsEqual 
    from 
         Patient p 
    inner join 
         [Resource] r on p.ProviderId = r.ResourceId
    where 
        PatientId = @PatientId
        and p.PrimaryCareProviderId = r.RefPhysId)

2 个解决方案

#1

You need to return the value from your stored procedure.

您需要从存储过程中返回值。

SELECT @isEqual

Aside that you need a SqlConnection object and a SqlCommand object to invoke the stored procedure.

除此之外，还需要一个SqlConnection对象和一个SqlCommand对象来调用存储过程。

conn = new SqlConnection(connectionString);
conn.Open();
SqlCommand cmd  = new SqlCommand("IsProviderSameAsPCP", conn);
cmd.CommandType = CommandType.StoredProcedure;
rdr = cmd.ExecuteReader();

You can then use the rdr object to loop through the result set.

然后可以使用rdr对象对结果集进行循环。

You can find your connection string at:

你可在以下网址找到你的连接串:

http://www.connectionstrings.com/

I.e. for SQL Server 2008:

即SQL Server 2008:

string connectionString = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;";

#2

You need to return the value in a select. Next line in your proc needs to be

您需要在select中返回值。您的proc中的下一行需要是

select @isEqual

ie.. declare @isEqual bit = (select top 1 1 as IsEqual from Patient p inner join [Resource] r on p.ProviderId = r.ResourceId where PatientId = @PatientId and p.PrimaryCareProviderId = r.RefPhysId) select @isEqual

即. .声明@isEqual位=(从患者p内部连接[资源]r中选择top 1作为IsEqual)。ProviderId = r。ResourceId, PatientId = @PatientId和p。PrimaryCareProviderId = r.RefPhysId)选择@isEqual。

ExecuteScalar is the command in C# you are looking for. You could also use an output parameter on your stored proc if you have multiple values and don't want to return a table output.

ExecuteScalar是您正在查找的c#中的命令。如果您有多个值，并且不想返回表输出，那么也可以在存储的proc上使用输出参数。

#1