PDA

View Full Version : Update Statements



TedMosby
11-26-2008, 03:31 AM
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
11-26-2008, 02:23 PM
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
11-27-2008, 06:50 AM
Thanks will do :)