November 23, 2005 at 5:22 pm
I have a stored proc that calls others. But it stops executing after the first IF fails. I know I am missing something basic and simple. help!!!!
CREATE PROCEDURE dbo.procedure1
@account numeric
AS
-- DECLARE AND INITIALIZE
DECLARE @response int
SET @response=0
-- BEGIN LOGIC
EXEC @response = dbo.response1 @account
IF @response = 1
-- response 1
EXEC @response = dbo.response2 @account
IF @response = 1
---response 2
EXEC @response = dbo.response3 @account
IF @response = 1
-- response 3
EXEC @response = dbo.response4 @account
IF @response = 1
-- response 4
SET @response =1
SELECT @response AS response
GO
November 24, 2005 at 4:44 am
Is the first one throwing an exception and SQL Server doing its usual trick of a batch abort?
November 24, 2005 at 8:49 am
Well based on what you have here you are stacking your if's and not nesting them.
Depending on what you desire. It is hard to guess your logic without more info.
But in essence when @response is not 1 after the first call, it will never change and nothing will get called.
But, this is basically what you are running
EXEC @response = dbo.response1 @account
IF @response = 1
BEGIN
-- response 1
EXEC @response = dbo.response2 @account
END
IF @response = 1
BEGIN
---response 2
EXEC @response = dbo.response3 @account
END
IF @response = 1
BEGIN
-- response 3
EXEC @response = dbo.response4 @account
END
IF @response = 1
BEGIN
-- response 4
SET @response =1
END
November 28, 2005 at 10:11 am
Thank you so much for both replys to my post;
What I want is for the store proc to evaluate all of the conditions; if they are true or if they = 1 for it to set the response to 1 otherwise to set the response to 0.
I hope that makes sense.
Thanks again
AB
November 28, 2005 at 1:10 pm
This is exactly what I am running.
CREATE PROCEDURE dbo.offer_ckcd
@account numeric
AS
-- DECLARE AND INITIALIZE
DECLARE @response int
SET @response=0
-- BEGIN LOGIC
BEGIN
EXEC @response = dbo.active_ckg @account
IF @response = 1
-- HAS ACTIVE CHECKING
BEGIN
EXEC @response = dbo.Ck_balance @account
IF @response = 1
---Checks if more than 0 balance on checking account
BEGIN
EXEC @response = dbo.add_ckcd_criteria @account
IF @response = 1
-- MEETS CRITERIA
BEGIN
EXEC @response = dbo.adult @account
IF @response = 1
-- IS AN ADULT
BEGIN
EXEC @response = dbo.CkCard_eligible @account
IF @response <>1
-- IS NOT ELIGIBLE FOR AN ATM
BEGIN
EXEC @response = dbo.below55 @account
IF @response = 1
-- IS BELOW 55 YEARS OLD
BEGIN
EXEC @response = dbo.GoodMember @account
IF @response = 1
----MEMBER IN GOOD STANDING
BEGIN
EXEC @response = dbo.no_ckcd @account
IF @response <>1
-- NO CHECKCARD
BEGIN
EXEC @response = dbo.no_ATM @account
IF @response <>1
-- NO ATM
SET @response = 1
END
END
END
END
END
END
END
END
END
SELECT @response AS response
GO
When I execute it I get the following
SET @response=0 -- BEGIN LOGIC
IF EXISTS(SELECT...........)
return 1
EXEC @response = dbo.active_ckg @account
IF @response = 1 -- HAS ACTIVE CHECKING
IF EXISTS(SELECT ...........)
return 1
EXEC @response = dbo.Ck_balance @account
IF @response = 1 ---Checks if more than 0 balance on checking account
IF EXISTS(SELECT ...........)
return 1
EXEC @response = dbo.add_ckcd_criteria @account
IF @response = 1 -- MEETS CRITERIA
IF EXISTS(SELECT ............)
return 1
EXEC @response = dbo.adult @account
IF @response = 1 -- IS AN ADULT
IF EXISTS(SELECT ...........)
return 1
EXEC @response = dbo.CkCard_eligible @account
IF @response <>1 -- IS NOT ELIGIBLE FOR AN ATM
SELECT @response AS response
OFFER_CKCD #####
I get response = 1; when I should get response = 0 because it did not return a 1 on the EXEC @response = dbo.CkCard_eligible @account.
I can't figure out what I am missing!
Any help would be very much appreciated.
AB
November 29, 2005 at 10:53 am
Your code is doing exactly what you tell it to do.
Logically you are saying
If HAS ACTIVE CHECKING then check some other things, otherwise we are done. Or another way of saying it, if they don't have active checking, then don't check anything else.
I believe that you want all the checks always run in which case you need this:
CREATE PROCEDURE dbo.offer_ckcd
@account numeric
AS
-- DECLARE AND INITIALIZE
DECLARE @response int
SET @response=0
-- BEGIN LOGIC
BEGIN
EXEC @response = dbo.active_ckg @account
IF @response = 1
-- HAS ACTIVE CHECKING
BEGIN
-- Do what you want
END
EXEC @response = dbo.Ck_balance @account
IF @response = 1
---Checks if more than 0 balance on checking account
BEGIN
-- Do whatever
END
....
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply