Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: UPDATE statement in stored proc will not accept WHERE MemberID = @MemberID

    Hey All,

    I Have the following query...

    Code:
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go
    
    ALTER PROCEDURE [dbo].[DRL_UD_ActivateMember]
    
    	@MemberID INT, @AsmID INT, @RetailGroup INT, @SecurityQuestionText VARCHAR(200), @SecutiryQuestionAnswer VARCHAR(50), @FirstName VARCHAR(30), @Surname VARCHAR(30),
    	@Email VARCHAR(75), @Mobile VARCHAR(20), @Gender VARCHAR(50), @DOBDay INT, @DOBMonth INT, @DOBYear INT, @HomeLine1 VARCHAR(100), @HomeLine2 VARCHAR(100),
    	@HomeSuburb VARCHAR(50), @HomeState VARCHAR(50), @HomePostcode VARCHAR(10), @HomeCountry VARCHAR(50), @WorkLine1 VARCHAR(100), @WorkLine2 VARCHAR(100),
    	@WorkSuburb VARCHAR(50), @WorkState VARCHAR(50), @WorkPostcode VARCHAR(10), @WorkCountry VARCHAR(50), @WorkPhone VARCHAR(20), @AgreeTerms BIT, @IsSubscribed BIT,
    	@DateActivated DATETIME
    
    AS
    BEGIN
    
    	UPDATE DRL_Members
    
    	SET AsmID = @AsmID, RetailGroupID = @RetailGroup, SecurityQuestionText = @SecurityQuestionText, SecurityQuestionAnswer = @SecutiryQuestionAnswer, 
    	Firstname = @FirstName, Surname = @Surname,	Email = @Email, Mobile = @Mobile, Gender = @Gender, DOBDay = @DOBDay, DOBMonth = @DOBMonth, DOBYear = @DOBYear, 
    	HomeLine1 = @HomeLine1, HomeLine2 = @HomeLine2,	HomeSuburb = @HomeSuburb, HomeState = @HomeState, HomePostcode = @HomePostcode, HomeCountry = @HomeCountry, 
    	WorkLine1 = @WorkLine1, WorkLine2 = @WorkLine2,	WorkSuburb = @WorkSuburb, WorkState = @WorkState, WorkPostcode = @WorkPostcode, WorkCountry = @WorkCountry, 
    	WorkPhone = @WorkPhone, AgreeTerms = @AgreeTerms, IsSubscribed = @IsSubscribed,	DateActivated = @DateActivated
    
    	WHERE MemberID = @MemberID
    
    END
    GO
    However the stored proc does not update the table...

    When I change the query string to be WHERE MemberID = 123456 the query runs fine (there is a member 123456 in the db)
    when i try to execute the exact same data in the prob, but have @MemberID set to 123456 the query executes, but failes to update that row.

    I am just wondering if there is a specific reason for this?

    CHeers,
    Justin

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Can you show the code you use to execute please?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How are you executing the proc?
    Are you certain that member id is being passed?
    Is the datatype for DRL_Members.MemberID the same as that of @MemberID?
    George
    Home | Blog

  4. #4
    Join Date
    Aug 2005
    Posts
    55
    I am unable to show you the code that actually executes the query, as that is in a seperate DLL file for executing stored procs...

    using SQL Server Profiler I was able to determin that the stored procedure was being called correctly, with @MemberID = 123456...

    And even when right clicking on the stored proc in Management studio, and going execute, populating valid properties it still ignores the @MemberID parameter, yet when I alter the proc to be WHERE MemberID = 123456 and reexecute that in management studio, the table is updated...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can call using T-SQL:
    Code:
    EXEC dbo.DRL_UD_TestMember @MemberID = 12345
    Code:
    ALTER PROCEDURE [dbo].[DRL_UD_TestMember]
    
        @MemberID INT
    
    AS
    BEGIN
    
        SELECT * FROM DRL_Members
        WHERE MemberID = @MemberID
    
    END
    GO
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Just out of curiousity, using SSMS (SQL Server Management Studio) try to execute:
    Code:
    SELECT *
       FROM INFORMATION_SCHEMA.TABLES
       WHERE  'DRL_Members' = TABLE_NAME
    What are the results?

    -PatP

  7. #7
    Join Date
    Aug 2005
    Posts
    55
    Quote Originally Posted by Pat Phelan
    Just out of curiousity, using SSMS (SQL Server Management Studio) try to execute:
    Code:
    SELECT *
       FROM INFORMATION_SCHEMA.TABLES
       WHERE  'DRL_Members' = TABLE_NAME
    What are the results?

    -PatP
    Here are the results of that query:

    Table_catalog -> Rewards (My database name)
    table_scheme -> dbo
    table_name -> DRL_Members
    table_type -> BASE TABLE

    so all that appears correct...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •