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 (

    @user-id,

    @adsource

    )

    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