Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Update Statements

    When I run my update query in Access it updates the 61 rows the query is supposed to update.

    When I run the same update query in SQL Server it updates the 61 rows as it should.

    My problem is that when I create a Stored Proc with paremeters it doesnt do the same as the manual update query it doesnt update any rows. Why would that be?

    Access Code
    Code:
    UPDATE PARAMED_temp SET PARAMED_temp.ContactServiceID = "LEARN Child"
    WHERE (((PARAMED_temp.StaffID) In ("l36","l37","l39")));
    SQL Server Manual Code
    Code:
    UPDATE PARAMED_TEMP_BUILD 
    SET PARAMED_TEMP_BUILD.ContactServiceID = 'LEARN CHILD'
    WHERE PARAMED_TEMP_BUILD.StaffID IN ('L36', 'L37', 'L39')
    Stored Procedure
    Code:
    ALTER PROCEDURE [jez].[sp_PARA_Update19a]
    	@ContServID VARCHAR(25),
    	@StaffID VARCHAR(10)
    AS
    UPDATE PARAMED_TEMP_BUILD 
    SET PARAMED_TEMP_BUILD.ContactServiceID = @ContServID
    WHERE PARAMED_TEMP_BUILD.StaffID IN ('+ @StaffID +')
    EXEC Statement
    Code:
    EXEC sp_PARA_Update19a 'LEARN CHILD', 'L36, L37, L39'
    Why is there a difference between them?

  2. #2
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Because a parameter is a single value, not a list of values (same is true in Access as far as I know).

    You could put several optional parameters in your proc:

    ...
    WHERE PARAMED_TEMP_BUILD.StaffID IN (@StaffID1,@StaffID2,@StaffID3)

    A range of other solutions are given here:
    http://www.sommarskog.se/arrays-in-sql-2005.html

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Excellent thanks for that

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd actually be rather surprised if you had any rows where the value of the PARAMED_TEMP_BUILD.StaffID column happened to be '+ @StaffID +'.

    You aren't dynamically creating SQL within your UPDATE statement, you are looking for the literal value '+ @StaffID +' and I'd be surprised if that is what you intended.

    -PatP

Posting Permissions

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