Robert Robert - 18 days ago 5
SQL Question

Calling a stored procedure and checking the return value

I have a stored procedure called

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

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?

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)

Answer

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.

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.

You can find your connection string at:

http://www.connectionstrings.com/

I.e. for SQL Server 2008:

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