stored procedure??
-
My stored procedure always reutrns -1
??
I am trying to test and see if the user id already exists in the database and if it does it should return -1 and if not then it should enter the data.
AS
DECLARE @rowcount INT
Select @rowcount = @@rowcount
IF @rowcount > 0
Return -1
ELSE
INSERT dbo.User_CIS (
User_ID,
Ad_Source
)
VALUES (
@UserID,
@AdSource
)
IF @rowcount > 0
Begin
UPDATE dbo.MySurvey_Campaign SET Current_Resume = @CurrentResume, Satisfied_Resume = @ResumeSatisfied, Ads_Answered = @AdsAnswered, Interviews_Attended = @AdsInterviews, Offers_received = @AdsOffers, Recruiters_Contacted = @Recruiters, Recruiters_Interviews = @RecruitersInterviews, Recruiters_Offers = @RecruitersOffers, Other_Contacts = @OtherContacts, Other_Interviews = @OtherInterviews, Other_Offers = @OtherOffers, Prospects_Pending = @ProspectsPending, Good_Prospects = @GoodProspects, Career_Services_Used = @ExploredCareerServices, Career_Services = @CareerServices, Comments = @Comments
WHERE User_ID = @UserID
END
ELSE
Begin
INSERT dbo.MySurvey_Campaign(User_ID, Current_Resume, Satisfied_Resume, Ads_Answered, Interviews_Attended, Offers_received, Recruiters_Contacted, Recruiters_Interviews, Recruiters_Offers, Other_Contacts, Other_Interviews, Other_Offers, Prospects_Pending, Good_Prospects, Career_Services_Used, Career_Services, Comments)
VALUES (@UserID, @CurrentResume, @ResumeSatisfied, @AdsAnswered, @AdsInterviews, @AdsOffers, @Recruiters, @RecruitersInterviews, @RecruitersOffers, @OtherContacts, @OtherINterviews, @OtherOffers, @ProspectsPending, @GoodProspects, @ExploredCareerServices, @CareerServices, @Comments)
END
Select @rowcount = @@rowcount
IF @rowcount > 0
Begin
UPDATE dbo.User_Dependents_Assets SET User_SO_Status = @MaritalStatus, SO_Name = @SpouseName, Dependents = @Dependents, Dependents_Age = @ChildrenAges, SO_Work = @SpouseWork, SO_Position = @SpousePosition, SO_Income = @SpouseIncome, Additional_Income = @AdditionalIncome, SO_Support_Campaign = @SpouseSupportive, SpouseEmployment = @SpouseEmployment
WHERE User_ID = @UserID
END
ELSE Begin
INSERT dbo.User_Dependents_Assets(User_ID, User_SO_Status, SO_Name, Dependents, Dependents_Age, SO_Work, SO_Position, SO_Income, Additional_Income, SO_Support_Campaign, SpouseEmployment)
VALUES (@UserID, @MaritalStatus, @SpouseName, @Dependents, @ChildrenAges, @SpouseWork, @SpousePosition, @SpouseIncome, @AdditionalIncome, @SpouseSupportive, @SpouseEmployment)
END
update dbo.User_Information Set Birth_Date = @DateofBirth
Where User_ID = @UserID
-
In the beginning of your procedure. your setting @rowcount = @@rowcount. Since no operation or query is being run before you set this, it is setting it to 1 causing your procedure to bail.
DECLARE @rowcount INT
Select @rowcount = @@rowcount
IF @rowcount > 0
Return -1
I would remove that first IF @rowcount > 0
Return -1
-
That didn't work.
I have tried moving
IF @rowcount > 0
Return -1
to the end of the insert statement, and it added the data and it also returned -1
-
I'm confused...if you insert something then your rowcount is going to be greater than 1 and your procedure is going to return -1
And where in the procedure does it say:
if exists(select userID from db....) ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
Does your statement look like this?
DECLARE @rowcount INT
INSERT dbo.User_CIS (
User_ID,
Ad_Source
)
VALUES (
)
Select @rowcount = @@rowcount
IF @rowcount > 0
Return -1
This will always evaluate to true, because after your insert, @rowcount will =1, which is Greater than 0, so it will always return.
Perhaps if you did
IF @rowcount = 0
Return -1
Meaning if nothing inserted then abort.
-
But nowhere is there any check for
"if the user id already exists in the database".....
isn't the requirement that the procedure return -1 if the userid exists and if not...then process.... ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
True that!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply