nbotts
asked on
stored procedure for bit conversion
Hello,
I need some help creating a stored procedure that will do a bit conversion for three checkboxes that I have on a form. Currently the form submits information as "yes" or "no" and is causing an error when it hits the database which is using a binary value for those responses. I would like to call a stored procedure that does this process for me. I think I'm close, but am unsure how to get it to enter it into multiple columns.
Here is what I have so far:
create procedure convertbits (@checkbox nvarchar(10) )
as
begin
declare @checkconvert bit
if (@checkbox = 'Yes') set @checkconvert = 1 else set @checkconvert = 0
insert into usertable(checkboxcolumn1) values (@checkconvert)
end
How do I properly have it do this for checkboxcolumn2 and checkboxcolumn3 within the same stored procedure?
I need some help creating a stored procedure that will do a bit conversion for three checkboxes that I have on a form. Currently the form submits information as "yes" or "no" and is causing an error when it hits the database which is using a binary value for those responses. I would like to call a stored procedure that does this process for me. I think I'm close, but am unsure how to get it to enter it into multiple columns.
Here is what I have so far:
create procedure convertbits (@checkbox nvarchar(10) )
as
begin
declare @checkconvert bit
if (@checkbox = 'Yes') set @checkconvert = 1 else set @checkconvert = 0
insert into usertable(checkboxcolumn1)
end
How do I properly have it do this for checkboxcolumn2 and checkboxcolumn3 within the same stored procedure?
ASKER
Thank you, so just one more question...How do I properly add the other checkboxcolumns?
e.g. (checkboxcolumn1,checkboxc olumn2, checkboxcolumn3) ?
e.g. (checkboxcolumn1,checkboxc
create procedure convertbits (@checkbox1 nvarchar(10) , @checkbox2 nvarchar(10), @checkbox3 nvarchar(10))
as
begin
declare @checkconvert bit , @box1 bit, @box2 bit, @box3 bit
set @box1= case when (@checkbox1 = 'Yes') then 1 else 0 end
set @box2= case when (@checkbox2 = 'Yes') then 1 else 0 end
set @box3= case when (@checkbox3 = 'Yes') then 1 else 0 end
set @checkconvert = case when (@box1 |@box2 | @box3) = 1 then 1 else 0 end
insert into usertable(checkboxcolumn1) values (@checkconvert)
end
as
begin
declare @checkconvert bit , @box1 bit, @box2 bit, @box3 bit
set @box1= case when (@checkbox1 = 'Yes') then 1 else 0 end
set @box2= case when (@checkbox2 = 'Yes') then 1 else 0 end
set @box3= case when (@checkbox3 = 'Yes') then 1 else 0 end
set @checkconvert = case when (@box1 |@box2 | @box3) = 1 then 1 else 0 end
insert into usertable(checkboxcolumn1)
end
ASKER
Do I need to add the other checkboxcolumns within this statement?:
insert into usertable(checkboxcolumn1) values (@checkconvert)
insert into usertable(checkboxcolumn1)
That is a question only you can answer....I don't know what you're trying to capture...if you want them stored there, then sure.
ASKER
No, what I mean is that the statement currently says "insert into usertable(checkboxcolumn1) values (@checkconvert)"
But that looks as though it is for one column only. Do I need to add checkboxcolumn2 and checkboxcolumn3 to that statement, and if so how?
But that looks as though it is for one column only. Do I need to add checkboxcolumn2 and checkboxcolumn3 to that statement, and if so how?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Fantastic! Thanks much, that did the trick.
as
begin
declare @checkconvert bit
set @checkconvert = case when (@checkbox = 'Yes') then 1 else 0 end
insert into usertable(checkboxcolumn1)
end