0

I'm using MS SQL server 2008 and I have to update a long list of names in one of the tables. I would like to pass a list of names and have them updated by adding a prefix to them. Say I pass X,Y,Z to the stored procedure and it updates them to Disable_X, Disable_Y, Disable_Z.

I think table Value parameters is what I'm looking for and I've read http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL but it only talks about inserting multiple parameters and what I'm trying to do is to update multiple records if they exist. I'm just starting to learn these concepts, so could someone point me in the right direction?

EDIT: I would like to pass a list of names that I want to update to a stored procedure. Instead of having to call the stored procedure multiple times, I only want to call it once.

user2696565
  • 587
  • 1
  • 8
  • 17
  • 1
    It's not very clear what you want. You need a stored procedure with a string(?) or table(?) parameter that you cann call to update your table? Or is a simple `update...where...` sufficent? – Ocaso Protal Oct 25 '13 at 10:42
  • @OcasoProtal Sorry for not being clear enough. I would like to call a stored procedure with a list of names that I want to update. This list could be a passed as a TVP or a comma separated list or any other way, whichever is easier to implement. If a simple update,where combination can do the trick thats also fine. – user2696565 Oct 25 '13 at 10:53

1 Answers1

0

This stored procedure expects a string consisting of comma-separated names. Convert this string to xml and use xml functionallity to get a list of all elements of the xml. Untested:

CREATE PROCEDURE [dbo].[MultiUpdate] 
(   
    @valuelist varchar(MAX) 
) AS 

    DECLARE @xml as xml

    SET @xml = cast(('<X>'+replace(@valuelist,',' ,'</X><X>')+'</X>') as xml)

    UPDATE yourtable SET name='DISABLE_'+name Where name in (SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N))

    RETURN
Ocaso Protal
  • 19,362
  • 8
  • 76
  • 83