Create PROCEDURE USP_IsSearchFinished @Id INT
AS
DECLARE @r BIT;
SELECT @r = COUNT(1)
FROM Table1 AP WITH (NOLOCK)
INNER JOIN Table2 SB WITH (NOLOCK) ON AP.ID = SB.Id
WHERE AP.ID = @Id
AND AP.Flag = 'Finished'
RETURN @r
GO
-- How to execute it in sql server
DECLARE @t BIT
EXEC @t = USP_IsSearchFinished 1
PRINT @t
-- Now, get that in ADO.NET
public int IsSearchFinished(int id)
{
int _id = 0;
try
{
using (SqlConnection connection = new SqlConnection(conString))
{
using (SqlCommand command = new SqlCommand("USP_IsSearchFinished", connection))
{
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@Id", id);
SqlParameter returnParameter = command.Parameters.Add("RetVal", SqlDbType.Bit);
returnParameter.Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
_id = (int)returnParameter.Value;
}
}
}
catch
{
}
return _id;
}
Cheers!