2

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

Community
  • 1
zerodoc
  • 235
  • 3
  • 9

1 Answers1

2

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.

Kin Shah
  • 60,908
  • 5
  • 111
  • 230
  • Above is correct and worked but I needed unique names added to the Stored procedure which I found [here](http://stackoverflow.com/questions/886293/how-do-i-execute-a-stored-procedure-once-for-each-row-returned-by-query) – zerodoc May 23 '14 at 20:19