Updating varchar column

Question

Updating varchar column

mtz676

SSCrazy Eights

Points: 9212
More actions
September 11, 2009 at 12:11 pm

#135634

I have a front end application which has 4 checkboxes . Each checkbox corresponds to a column in the database . A selection of a particular checkbox would be a 1 in the database . If unselected it is a 0 in the database . There is an additional column which captures all the columns which have a 1 in their column for their corresponding rows . So 1 row can have more than 1 selection of checkboxes . How do I accomplish this using a stored procedure . Using a stored procedure I have been able to capture only 1 value in the G column and not multiple values . G is a varchar column .
Ex: 1 row . A B C D G
ABC 1 0 0 0 A
DEF 0 0 0 1 D,E,F
GHF 1 1 0 0 A,B,F
Responses are appreciated .
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your psuedo code for your table wasn't readable to me, and oyu didn't provide any real, concrete CREATE TABLE or data for us to use.

here's a quick example i fleshed out and tested, this might get you pointed in the right direction:

create table example(exampleid int identity(1,1) not null primary key,

check1 integer,

check2 integer,

check3 integer,

check4 integer,

MyCollectionOfChecks AS

CASE WHEN check1 = 1 then 'A ' ELSE '' END +

CASE WHEN check2 = 1 then 'B ' ELSE '' END +

CASE WHEN check3 = 1 then 'C ' ELSE '' END +

CASE WHEN check4 = 1 then 'D ' ELSE '' END,

OtherColumns varchar(30))

insert into example(check1,check2,check3,check4)

select 0,0,0,0 union all

select 1,0,1,0

GO

create procedure UpdateMyCheckboxes(@id int,@ck1 int,@ck2 int,@ck3 int,@ck4 int)

AS

BEGIN

UPDATE example

SET check1 = @ck1,

check2 = @ck2,

check3 = @ck3,

check4 = @ck4

WHERE exampleid = @id

END

SELECT * FROM example

EXECUTE UpdateMyCheckboxes 1,0,1,1,1

EXECUTE UpdateMyCheckboxes 2,0,1,0,1

SELECT * FROM example

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

How do I automatically insert commas between the values that come into the OtherColumns column from the 4 checked columns check1 ,check2 ,check3,check4 ? Thanks