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