How to cast exception in SQL and treat in C#

Question

I’m creating procedures in an SQL Server database. To clear a brand, I first check if the brand is already tied to a product before deleting.

CREATE PROCEDURE SP_Delete_MarcasProdutos
@Id int
AS
BEGIN
    DECLARE @AUX int
    SET @AUX = (select top 1 p.Id from Produtos p where p.MarcaProduto_Id = @Id AND p.Excluido = 0)

    IF(@AUX IS NOT NULL)
        BEGIN
            PRINT 'Não foi possível excluir esta marca, pois ela possui relacionamentos com produtos ativos';
            THROW;
        END
    ELSE
        UPDATE MarcasProdutos SET Excluido = 1 WHERE Id = @Id
END

The procedure in If works? By releasing this exception, this will be the message captured by C#?

Answer 1

You can use the RAISEERROR with a severity level of at least 10 (smaller numbers do not cause exception). This will produce an exception in C# that can be captured by SQLException.