Friday 18 September 2015

Return parameter in sql server



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!