quote:

---

Originally posted by kjmcsd

I am confused about how to update a group of records with SQL inside a stored proc.

Let's say I have 3 columns.

ID int

Name varchar(100)

Confirm bit



I want to update the confirm column for 10 names with one call to the stored proc. How do I do it? What do I need to pass to the stored proc?

---

create procedure updatetest
@name varchar(500), 
@CONFIRM varchar(1)
as 
DECLARE @SQLSTRING NVARCHAR(500);
SET @SQLSTRING = N'UPDATE TEST1 SET CONFIRM = '+@CONFIRM+' where name in ('+@name+')';
EXECUTE SP_EXECUTESQL @SQLSTRING;
--end of proc

EXECUTE updatetest '''a'',''b'',''c''', '0'

quote:

---

Originally posted by kjmcsd

Ok I think I understand now due to research of SQL IN. I do not think that will work.

Can you post your suggestion using SQL?

---

create procedure updatetest
@name varchar(MAX), 
@CONFIRM varchar(MAX)
as 
DECLARE @SQLSTRING NVARCHAR(MAX);
SET @SQLSTRING = N'UPDATE TEST1 SET CONFIRM = '+@CONFIRM+' where name in ('+@name+')';
EXECUTE SP_EXECUTESQL @SQLSTRING;
GO
--end of proc

EXECUTE updatetest '''a'',''b'',''c''', 'CASE name WHEN ''a'' then 1 when ''b'' then 0 when ''c'' then 1 end'

quote:

---

Originally posted by kjmcsd

My fault I should have added this. Each confirm value for each name could be different. In that case that would not work either.

---