Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Great that works, one more thing how would I add a parameter to the SendMail @body = a.SupplyName? –  zerodoc May 23 at 19:23
    
Is the supply name going to be same or unique ? –  Kin May 23 at 19:45
    
Yes unique to the row with the alert status of 'Y' –  zerodoc May 23 at 19:49
    
Is this what I need to do link –  zerodoc May 23 at 20:05

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Above is correct and worked but I needed unique names added to the Stored procedure which I found here –  zerodoc May 23 at 20:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.