Update Statements

[TedMosby]

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

UPDATE PARAMED_temp SET PARAMED_temp.ContactServiceID = "LEARN Child"
WHERE (((PARAMED_temp.StaffID) In ("l36","l37","l39")));

SQL Server Manual Code

UPDATE PARAMED_TEMP_BUILD 
SET PARAMED_TEMP_BUILD.ContactServiceID = 'LEARN CHILD'
WHERE PARAMED_TEMP_BUILD.StaffID IN ('L36', 'L37', 'L39')

Stored Procedure

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

EXEC sp_PARA_Update19a 'LEARN CHILD', 'L36, L37, L39'

Why is there a difference between them?

[Mavyak]

It is treating your in-list as one value instead of all the values comma separated. Try this:

ALTER PROCEDURE [jez].[sp_PARA_Update19a]
 @ContServID VARCHAR(25),
 @StaffID VARCHAR(10)
AS
DECALRE @SQL

SET @ SQL = 'UPDATE PARAMED_TEMP_BUILD SET PARAMED_TEMP_BUILD.ContactServiceID = ' + REPLACE(@ContServID, '''', '''''') + ' WHERE PARAMED_TEMP_BUILD.StaffID IN (''' + REPLACE(@StaffID, ',', ''',''') + ''')'
EXEC (@SQL)

[TedMosby]

Thanks will do