Stored Procedure to evaluate column if condition is true execute another stored procedure

Question

I have a stored procedure that executes with Windows task manager and I want to add a condition statement that executes another stored procedure if the condition is true. I was thinking a case statement but I get a invalid syntax near CASE and according to this response executing a stored procedure in a select or case clause is not allowed how would I go about executing a stored procedure on a table if a condition is met.

This is the stored procedure:

 SET NOCOUNT ON;

 UPDATE Supply
 SET [Quantity] = [Quantity] - [SupplyHalfLife] 

 UPDATE Supply  
 SET SupplyAlert = CASE WHEN Quantity <= SupplyQuantityAlert THEN 'Y' ELSE 'N' END

and this is what I want to add to it:

SELECT a.SupplyName
CASE a.SupplyAlert
WHEN 'Y' THEN dbo.sp_SendMail @body = a.SupplyName END
FROM Supply a

which checks column for a alert status of 'Y' and executes a stored procedure.

Code I finally used:

DECLARE @name varchar(255)

DECLARE cur CURSOR LOCAL FOR
SELECT SupplyName FROM Supply WHERE SupplyAlert = 'Y'

OPEN cur

FETCH NEXT FROM cur INTO @name

WHILE @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    EXEC dbo.SendMail @name

    FETCH NEXT FROM cur INTO @name
END

CLOSE cur
DEALLOCATE cur

link

Answer 1

how would I go about executing a stored procedure on a table if a condition is met.

if exists (select 1 from Supply where SupplyAlert = 'Y')
begin 
exec dbo.sp_SendMail 
end 
else print 'Nothing to send !' 

Remember that CASE is a function and not a control flow statement.