Update Table from List of Values in a String Without Dynamic SQL

Posted: April 14, 2011 in SQL Server 2005

This example shows how you can update table records without dynamic SQL from values in a string.

-- Declare variables
DECLARE @lv_string VARCHAR(20);

-- Cretae temp table
CREATE TABLE #tmp_upd (id          INT IDENTITY(1,1),
                       email       VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
                       email_group VARCHAR(20)  COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL);

-- Insert records
INSERT INTO #tmp_upd (email, email_group) VALUES ('a@a.com', 'A');
INSERT INTO #tmp_upd (email, email_group) VALUES ('b@a.com', 'B');
INSERT INTO #tmp_upd (email, email_group) VALUES ('c@a.com', 'C');
INSERT INTO #tmp_upd (email, email_group) VALUES ('d@a.com', 'D');
INSERT INTO #tmp_upd (email, email_group) VALUES ('e@a.com', 'E');

-- Select the records
SELECT * FROM #tmp_upd;

-- Now say we receive a list of values in a string as follows 'A,C,E' that need to be updated to 'X'
SET @lv_string = 'A,C,E';

-- We can update this in one hit using the table valued functon as follows
UPDATE #tmp_upd
SET    email_group = 'X'
WHERE  email_group IN (SELECT item COLLATE SQL_Latin1_General_CP1_CI_AS
                       FROM   dbo.FN_SplitCharByDelimiterIncNull(@lv_string, ','));

-- Select the records
SELECT * FROM #tmp_upd;

-- Drop temp table
DROP TABLE #tmp_upd;

You can get the table valued function for splitting a string from this post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s