?
Solved

stored procedure for bit conversion

Posted on 2009-04-16
8
Medium Priority
?
179 Views
Last Modified: 2012-05-06
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?
0
Comment
Question by:nbotts
  • 4
  • 4
8 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 24161047
create procedure convertbits (@checkbox nvarchar(10) )
as
begin
declare @checkconvert bit
set @checkconvert = case when (@checkbox = 'Yes') then 1 else 0 end
insert into usertable(checkboxcolumn1) values (@checkconvert)
end
0
 

Author Comment

by:nbotts
ID: 24161099
Thank you, so just one more question...How do I properly add the other checkboxcolumns?

e.g. (checkboxcolumn1,checkboxcolumn2, checkboxcolumn3) ?
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24161142
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:nbotts
ID: 24161216
Do I need to add the other checkboxcolumns within this statement?:

insert into usertable(checkboxcolumn1) values (@checkconvert)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24161228
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.  
0
 

Author Comment

by:nbotts
ID: 24161307
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?
0
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 24161870
First, you'll need to add those columns to the table:

alter table usertable
add checkboxcolumn2 bit, checkboxcolumn3 bit

then you'll need to alter your proc:

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

insert into usertable(checkboxcolumn1, checkboxcolumn2, checkboxcolumn3) values (@box1, @box2, @box3)
end
0
 

Author Closing Comment

by:nbotts
ID: 31571136
Fantastic! Thanks much, that did the trick.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question