IT seems to me that I can't write something like this:
DECLARE @.Exists bit
SELECT @.Exists=(UserId Is NOT NULL)
FROM User
WHERE FirstName="Some name" And LastNAme="Other"
I tries this and got an error. I am using Case to get around it. Just wanted
to know that I am correct. Is there other way doing the same thing?
Thanks,
Shimon.There is no boolean datatype in SQL Server. An alternative to a CASE
expression is an IF statement:
DECLARE @.Exists bit
IF EXISTS
(
SELECT *
FROM User
WHERE FirstName="Some name" AND
LastName="Other" AND
UserId IS NOT NULL
)
SET @.Exists = 1
ELSE
SET @.Exists = 0
Hope this helps.
Dan Guzman
SQL Server MVP
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Boolean expressions are valid in a WHERE clause, in CASE expressions
and in other places but can't be assigned directly to a variable
because there is no explicit boolean datatype in SQL Server. Use CASE
to convert a boolean expression to a valid datatype.
David Portas
SQL Server MVP
--|||You need a good SQL book.
1) The double quotes in Standard SQL are used to mark an identifier;
strings have always used single quotes.
2) There are no BOOLEAN or BIT data types in SQL. They would be a
total screw up. Why? Because machine level things like a BIT or BYTE
datatype have no place in a high level language like SQL. SQL is a
high level language; it is abstract and defined without regard to
PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!
There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!
You have a major learning curve in front of you.
I usually tell peopel it takes at least a year of full time programming
to UN-learn their origianl language.|||
--CELKO-- wrote:
>You need a good SQL book.
>1) The double quotes in Standard SQL are used to mark an identifier;
>strings have always used single quotes.
>2) There are no BOOLEAN or BIT data types in SQL. They would be a
>total screw up. Why? Because machine level things like a BIT or BYTE
>datatype have no place in a high level language like SQL. SQL is a
>high level language; it is abstract and defined without regard to
>PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
You are mistaken. Both types appear in the ISO/IEC SQL standards.
BOOLEANs were introduced in SQL-99 (they correspond to PL/I's BIT(1) type)
and there was a BIT type in SQL-92 and early drafts of SQL-99, but it's gone
now, I believe.
>SQL is a high level language; it is abstract and defined without regard
>to PHYSICAL implementation. This basic principle of data modeling is
>called data abstraction.
>
Well-said!
>Bits and Bytes are the <i>lowest<i> units of hardware-specific,
>physical implementation you can get. Are you on a high-end or low-end
>machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
>complement or ones complement math? Hey, the standards allow decimal
>machines, so bits do not exist at all! What about NULLs? To be a SQL
>datatype, you have to have NULLs, so what is a NULL bit? By definition
>a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
>how are the bit-wise operations defined? Oh what a tangled web we
>weave when first we mix logical and physical :)
>
There is no confusion in SQL Server. The confusion is yours, in
confounding the
abstract type [bit] of SQL Server with the use of the word bit in
describing hardware.
They aren't the same thing at all. SQL Server [bit] behavior is
consistent, documented and
fully hardware-independent. [bit] values are 0 and 1, and it's possible for
a bit column or variable to be NULL. All the operations valid for [bit]
values
are well-defined and documented. If you think the documentation gets
any of them
wrong, please let us know.
>What does the implementation of the host languages do with bits? Did
>you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
>consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
>VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
>proprietary languages from the same vendor. That means <i>all<i> the
>host languages -- present, future and not-yet-defined -- can be
>different. Surely, no good programmer would ever write non-portable
>code by getting to such a low level as bit fiddling!!
>
There is no BOOLEAN type in SQL Server, and there are libraries to help
anyone using T-SQL with other languages bridge the gulf between different
type systems, which are expected with any pair of languages.
>There are usually two situations in practice. Either the bits are
>individual attributes or they are used as a vector to represent a
>single attribute. In the case of a single attribute, the encoding is
>limited to two values, which do not port to host languages or other
>SQLs, cannot be easily understood by an end user, and which cannot be
>expanded.
>
The bit type is generally non-portable, although like many other
common features not part of the standard, like SIGN, bit is not hard
to port when needed. It might be good to avoid one too-proprietary quirk,
in my opinion, that CAST(x as bit) evaluates to 1 when x is a non-zero
value of any numeric type. If that is avoided, it should port well to a
system
without a matching type if it is converted to the smallest available
integer type.
>In the second case what some Newbies, who are still thinking in terms
>of second and third generation programming languages or even punch
>cards, do is build a vector for a series of "yes/no" status codes,
>failing to see the status vector as a single attribute. Did you ever
>play the children's game "20 Questions" when you were young?
>Imagine you have six components for a loan approval, so you allocate
>bits in your second generation model of the world. You have 64 possible
>vectors, but only 5 of them are valid (i.e. you cannot be rejected for
>bankruptcy and still have good credit). For your data integrity, you
>can:
>
This is an interesting problem that has nothing to do with the original
post in this thread. It also does not have a single out-of-context answer.
>1) Ignore the problem. This is actually what <i>most<i> newbies do.
>2) Write elaborate CHECK() constraints with user defined functions or
>proprietary bit level library functions that cannot port and that run
>like cold glue.
>Now we add a 7-th condition to the vector -- which end does it go on?
>Why? How did you get it in the right place on all the possible
>hardware that it will ever use? Did all the code that references a bit
>in a word by its position do it right after the change?
>
Fortunately, [bit], like all SQL Server types, are hardware-independent.
There is no need to worry about this. Columns are referred to by their
names, and the underlying implementation is not exposed to the user.
>You need to sit down and think about how to design an encoding of the
>data that is high level, general enough to expand, abstract and
>portable. For example, is that loan approval a hierarchical code?
>concatenation code? vector code? etc? Did you provide codes for
>unknown, missing and N/A values? It is not easy to design such things!
>
>
It's preposterous and presumptions, in my opinion, to be telling the poster
how to design something irrelevant to the question asked.
>You have a major learning curve in front of you.
>I usually tell peopel it takes at least a year of full time programming
>to UN-learn their origianl language.
>
And it takes just a little bit of listening and research to divest
oneself of erroneous beliefs, like yours, that the SQL Server
[bit] type exposes hardware details and depends on
endianness and other nonsense. You have to be interested
in learning and growing up, that's all.
Steve Kass
Drew University|||Well, I don't know the answer to your specific question (and I'm sorry for
that), but I'm glad you posted it and I'm sure you will get an answer soon
as there are plenty of competent dbas in this group. In the mean time, I'd
like to take a moment to affirm you as an aspiring dba. Given the fact that
you are asking for help assures me that you are striving to become a better
person in at least one small way. And for that, you are in inspiration to
all of us and a huge asset to your employer. God bless, and have a great
day!"
Celko-2
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPhsPTwDFHA.3840@.tk2msftngp13.phx.gbl...
> IT seems to me that I can't write something like this:
> DECLARE @.Exists bit
> SELECT @.Exists=(UserId Is NOT NULL)
> FROM User
> WHERE FirstName="Some name" And LastNAme="Other"
> I tries this and got an error. I am using Case to get around it. Just
> wanted to know that I am correct. Is there other way doing the same thing?
> Thanks,
> Shimon.
>|||Joe --
I read through your customary belittling comments about Shimon's total lack
of
intelligence/skill/knowledge/etc. and something just jumped out at me --
You completely forgot the derogatory comment about the poster's command of t
he English
language!
From a previous Celko post --
Rows and records are TOTALLY different concepts. Since you did not use
plurals correctly in your English, can I assume you are Asian and
speak/write a language without them?
Now, because there were relatively few errors in Shimon's question (it was a
short post),
that may have kept the message below the acceptable threshhold for being com
mented on. If
not, this was a serious oversight on your part; one I feel needs to be recti
fied as soon
as possible (i.e., your next posting).
Thanks in advance,
Carl
Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts
Thursday, March 29, 2012
boolean programming
Is bit the closest to the boolean (true or false) datatype in tsql? Even
though it could be 0,1,null'
TIABIT is a numeric datatype. There is no assignable boolean datatype in SQL
Server - booleans are only valid for expressions.
If BIT doesn't suit then use a CHAR:
... b CHAR(1) NOT NULL CHECK (c IN ('T','F'))
David Portas
SQL Server MVP
--|||Not really, for a column which can have only two values, the best approach
in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
One could consider the Bit type to be closer to numeric datatypes like INT
or TINYINT since they have common values and several operators overlapping
among them.
Anith|||Hello Anith,
why would a CHAR(1) be the best approach?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:%23y0z1V5kFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Not really, for a column which can have only two values, the best approach
> in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
> One could consider the Bit type to be closer to numeric datatypes like INT
> or TINYINT since they have common values and several operators overlapping
> among them.
> --
> Anith
>|||> why would a CHAR(1) be the best approach?
Well, I'm not sure of the definition of "best"... but some benefits of
CHAR(1) are:
- you can store T/F or Y/N instead of 0/1.
- you eliminate confusion for VB/Access people, who often expect -1 to mean
true, and this does not work with BIT.
- you can easier implement indexes, group by, etc.
Drawbacks:
- it is no longer language-neutral (if this is an issue) since French would
expect v for vrai, or o for oui. Same goes for several other languages.
- it is subject to the same problems as BIT as far as NULLability goes.
- you lose the potential ability to save space in tables where multiple such
columns exist. Up to 8 BIT columns can share a single byte, whereas
TINYINT/CHAR(1) will always take 1 byte each.|||> Is bit the closest to the boolean (true or false) datatype in tsql? Even
> though it could be 0,1,null'
It really isn't all that difficult to use a BIT column with two-valued logic
as opposed to three-valued logic.
CREATE TABLE dbo.Example
(
TrueFalse BIT NOT NULL
)
Null problem solved, no?|||I actually disagree. I, when looking at boolean datatypes, tend to use
the bit datatype. By making it a NOT NULL column and setting a default
to 0, it should solve your problems. I know for a fact that vb/vb.net
interprets a bit value as boolean. I would recommend explicitly
casting the value to boolean, but it will work.|||The other drawback is that most tools now deal with bit nicely as a boolean
and the char approach requires the UI programmer to handle things
differently than they expect. And too often if the UI developer has to go
out of their way to do something it starts to look like we db folks are
being difficult "again" :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||Thanks Aaron for your explanation. I understand your point that one would
loose the potential ability to save space in tables but wouldn't bit columns
contradict using indexes? How is your feeling on bilcolumns in case of
performance and usability? And what would be an ideal approach to implement
a bit column?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||> wouldn't bit columns contradict using indexes?
Yes, indexes on bit columns are seldom useful, and I have never needed one,
but a lot of people complain that Enterprise Manager won't let you do it
(see http://www.aspfaq.com/2530).
> How is your feeling on bilcolumns in case of performance and usability?
I don't have any problems with them, though there are some minor details you
should be aware of. Again, see http://www.aspfaq.com/2530
> And what would be an ideal approach to implement a bit column?
I don't understand how you would change the approach? You either use BIT or
you don't?
though it could be 0,1,null'
TIABIT is a numeric datatype. There is no assignable boolean datatype in SQL
Server - booleans are only valid for expressions.
If BIT doesn't suit then use a CHAR:
... b CHAR(1) NOT NULL CHECK (c IN ('T','F'))
David Portas
SQL Server MVP
--|||Not really, for a column which can have only two values, the best approach
in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
One could consider the Bit type to be closer to numeric datatypes like INT
or TINYINT since they have common values and several operators overlapping
among them.
Anith|||Hello Anith,
why would a CHAR(1) be the best approach?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:%23y0z1V5kFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Not really, for a column which can have only two values, the best approach
> in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
> One could consider the Bit type to be closer to numeric datatypes like INT
> or TINYINT since they have common values and several operators overlapping
> among them.
> --
> Anith
>|||> why would a CHAR(1) be the best approach?
Well, I'm not sure of the definition of "best"... but some benefits of
CHAR(1) are:
- you can store T/F or Y/N instead of 0/1.
- you eliminate confusion for VB/Access people, who often expect -1 to mean
true, and this does not work with BIT.
- you can easier implement indexes, group by, etc.
Drawbacks:
- it is no longer language-neutral (if this is an issue) since French would
expect v for vrai, or o for oui. Same goes for several other languages.
- it is subject to the same problems as BIT as far as NULLability goes.
- you lose the potential ability to save space in tables where multiple such
columns exist. Up to 8 BIT columns can share a single byte, whereas
TINYINT/CHAR(1) will always take 1 byte each.|||> Is bit the closest to the boolean (true or false) datatype in tsql? Even
> though it could be 0,1,null'
It really isn't all that difficult to use a BIT column with two-valued logic
as opposed to three-valued logic.
CREATE TABLE dbo.Example
(
TrueFalse BIT NOT NULL
)
Null problem solved, no?|||I actually disagree. I, when looking at boolean datatypes, tend to use
the bit datatype. By making it a NOT NULL column and setting a default
to 0, it should solve your problems. I know for a fact that vb/vb.net
interprets a bit value as boolean. I would recommend explicitly
casting the value to boolean, but it will work.|||The other drawback is that most tools now deal with bit nicely as a boolean
and the char approach requires the UI programmer to handle things
differently than they expect. And too often if the UI developer has to go
out of their way to do something it starts to look like we db folks are
being difficult "again" :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||Thanks Aaron for your explanation. I understand your point that one would
loose the potential ability to save space in tables but wouldn't bit columns
contradict using indexes? How is your feeling on bilcolumns in case of
performance and usability? And what would be an ideal approach to implement
a bit column?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||> wouldn't bit columns contradict using indexes?
Yes, indexes on bit columns are seldom useful, and I have never needed one,
but a lot of people complain that Enterprise Manager won't let you do it
(see http://www.aspfaq.com/2530).
> How is your feeling on bilcolumns in case of performance and usability?
I don't have any problems with them, though there are some minor details you
should be aware of. Again, see http://www.aspfaq.com/2530
> And what would be an ideal approach to implement a bit column?
I don't understand how you would change the approach? You either use BIT or
you don't?
Boolean in SQL Server 2005
What is the boolean equivalent for TRUE and FALSE in SQL Server 2005?
In Access true = -1 and false = 0
What should I use in SQL Server bit values?0 = false; 1 = true|||Can I write statements like Match='TRUE' instead of Match=1?|||
No, you need to use 0 and 1. SQL Server has no knowledge of true and false. This is only possible by creating custom data type via .NET.
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
|||Hi,but this is not recommended, you should really use the internal used data types for this simple operations. Your application should transfer the value to refelect the appropiate SQL Statements and present the data types the way you want the users to see them.
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de|||why you want to do this. Do you have requirment to write a one code for both SQL Server and Access
boolean fields displayed as true or false
I have a bit field in my data source that displays as true/false on my
report. I can't see how to set this so that it displays as 1 or 0. In
Visual Studio, can someone help?=iif (Fields!yourData.value = "True", 1, 0)
"beeyule" wrote:
> I have a bit field in my data source that displays as true/false on my
> report. I can't see how to set this so that it displays as 1 or 0. In
> Visual Studio, can someone help?
report. I can't see how to set this so that it displays as 1 or 0. In
Visual Studio, can someone help?=iif (Fields!yourData.value = "True", 1, 0)
"beeyule" wrote:
> I have a bit field in my data source that displays as true/false on my
> report. I can't see how to set this so that it displays as 1 or 0. In
> Visual Studio, can someone help?
Tuesday, March 27, 2012
Boolean datatypes
Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
Boolean datatypes
Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kd
kd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kd
kd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||You are correct that there is no Boolean datatype in SQL Server. You can choose whatever you want to
represent what you want to call "true" and "false", these will only be values of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and bind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). There has been several
discussions ion this group and .programming on the subject and I recommend that you check the
archives and determine whether you want to use bit or, for example, char(1) with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other options exists).
(*) One strange thing with the bit datatype in SQL Server is that you will not get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be 1.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
Boolean datatypes
Hi All,
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can cho
ose whatever you want to
represent what you want to call "true" and "false", these will only be value
s of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and b
ind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). Ther
e has been several
discussions ion this group and .programming on the subject and I recommend t
hat you check the
archives and determine whether you want to use bit or, for example, char(1)
with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other opt
ions exists).
(*) One strange thing with the bit datatype in SQL Server is that you will n
ot get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be
1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
How to create fields with boolean datatypes? There seems to be no 'boolean'
SQL data type. Can the bit datatype be used to store 0 for false and 1 for
true?
Thanks,
kdkd,
There are no boolean datatype in SQL Server 7/2000. You can use bit (or
another "numeric" datatype, tinyint for example) or use a char(1)
storing "F"/"T" (or "Y"/"N" or whatever). Bit is normally "converted" to
a boolean by ADO (but I'm not sure if bit=0 is TRUE or FALSE, I _think_
it "means" FALSE...).
Lars Broberg
Elbe-Data AB
http://www.elbe-data.se
Remove "nothing." when replying to private e-mail!
kd wrote:
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||You are correct that there is no Boolean datatype in SQL Server. You can cho
ose whatever you want to
represent what you want to call "true" and "false", these will only be value
s of that datatype for
SQL Server. Some programming environments will consider bit as Boolean and b
ind such automatically;
but to SQL Server, bit is a numeric datatype restricted to 0 and 1 (*). Ther
e has been several
discussions ion this group and .programming on the subject and I recommend t
hat you check the
archives and determine whether you want to use bit or, for example, char(1)
with a check constraint
restricting to 't' pr 'f', 'y' or 'n' etc (see the old discussions other opt
ions exists).
(*) One strange thing with the bit datatype in SQL Server is that you will n
ot get an overflow if
you assign, for instance, the value 2 to a bit datatype. The result will be
1.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no 'boolean
'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd|||In addition to the other comments... When deciding whether to use bit or
char(1), you are (essentially) weighing the space savings of bit, versus the
ease of printing with a char(1)( no conversion on output)...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"kd" <kd@.discussions.microsoft.com> wrote in message
news:3F70CFF6-3B66-497A-B2D0-5A98E9D6F597@.microsoft.com...
> Hi All,
> How to create fields with boolean datatypes? There seems to be no
> 'boolean'
> SQL data type. Can the bit datatype be used to store 0 for false and 1 for
> true?
> Thanks,
> kd
Boolean bit field Which is True False
I have a field that I need to query for true and false.
The field is a bit.
Is True 0 or 1.
I can't open Books Online and the download instructions based on my version
SQL 2000, are not clear and I don't know what detailed version I have nor
where to find it.
--
MichaelMI guess it depends on how your app inserts the data...
create table foo (hidden bit)
insert into foo values ('Y')
select * from foo
error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Y' to a column of data type bit.
Keith Kratochvil
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
>I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
> version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Michael Miller wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM
A bit column is neither True nor False because bit is a numeric
datatype not a boolean. Usually 1 is understood to mean True and 0 is
understood to mean False. Of course it's always possible that the
person who designed your database may not have respected that
convention at all. One reason I dislike using the bit type is precisely
because of this ambiguity. A meaningful code makes it much clearer just
what the designer's intention is. On the other hand proper
documentation ought to answer your question in any case :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||They are what you want them to be; SQL server only stores the 0 or 1. In
Access, true is numerically represented by -1, and false is 0.
You could say that 1 is true and 0 is false, and extract data from the
column using "true" and "false" with a CASE expression:
SELECT
CASE
WHEN Column1 = 1 THEN 'True'
ELSE 'False'
END
FROM [Your Table]
"Michael Miller" wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||I think .Net automatically converts bit columns to 0=False and 1=True.
Outside of .Net it would be application specific. If you are going to use a
bit column, then I would make it a point to code around values of 0 and 1,
rather than True or False. You also need to take into account NULL values,
if this column allows them. BOL does not have much to say about this
datatype. Bottom line, bit is an integer value, and not a boolean value.
It is up to your code to determine how to use it. Here are some quotes from
BOL.
Converting bit Data
Converting to bit promotes any nonzero value to 1.
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on.
Special Data
Special data consists of data that does not fit any of the categories of
data such as binary data, character data, Unicode data, date and time data,
numeric data and monetary data.
Microsoft SQL ServerT 2000 includes four types of special data:
bit
Consists of either a 1 or a 0. Use the bit data type when representing TRUE
or FALSE, or YES or NO. For example, a client questionnaire that asks if
this is the client's first visit can be stored in a bit column.
Constants
A constant, also known as a literal or a scalar value, is a symbol that
represents a specific data value. The format of a constant depends on the
data type of the value it represents.
bit constants
bit constants are represented by the numbers zero or one, and are not
enclosed in quotation marks. If a number larger than one is used, it is
converted to one.
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||bit is an integer data type, and can be used to store 0, 1, or NULL.
You will need to define whether a value of 1 stands for true (or otherwise)
throughout your application.
- Data Types (SS2000 books online)
http://msdn.microsoft.com/library/d...br />
7msw.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> bl
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com g...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Thank you ALL.
I'll investigate and see what the intention of the creator was.
I'll start with 1=true/yes and 0=false/no
--
MichaelM
"Michael Miller" wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||>> I have a field [sic] that I need to query for true and false.<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Totally different concepts!
You are missing another basic point here. There is no BOOLEAN data
type in SQL and it is a sign of bad programming to write with lower
level bit flags in a high-level language.
Get a book on RDBMS and read about three-valued logic and NULLs to
learn why this cannot be implemented in the SQL model.
Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?|||Understood. I am querying and reporting and needed to know how they are
using a particular field, which is bit. I didn't set up the database and
have differed with the contractor on other design matters as well. For
example, we have two types of tables, one called SAG and the other called
TRG. Instead of making a 3 char field, he set up one fieldname that called
TRG, and then he says whether it is true or not (the bit field). Since he
has 0's and 1's, I was trying to figure out which was which.
I would have named the field GridType, and entered either SAG or TRG, for
readability.
I understand about the education. I have no formal education in SQL Server,
other than some OJT working on a VB6 project with SQL, for about 3 years.
Added to that is 5 years in Access and 15 years in Paradox (DOS 1.0 -->Win
8.0).
Thanks for your input.
--
MichaelM
"--CELKO--" wrote:
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Totally different concepts!
>
> You are missing another basic point here. There is no BOOLEAN data
> type in SQL and it is a sign of bad programming to write with lower
> level bit flags in a high-level language.
> Get a book on RDBMS and read about three-valued logic and NULLs to
> learn why this cannot be implemented in the SQL model.
> Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?
>
The field is a bit.
Is True 0 or 1.
I can't open Books Online and the download instructions based on my version
SQL 2000, are not clear and I don't know what detailed version I have nor
where to find it.
--
MichaelMI guess it depends on how your app inserts the data...
create table foo (hidden bit)
insert into foo values ('Y')
select * from foo
error:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'Y' to a column of data type bit.
Keith Kratochvil
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
>I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
> version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Michael Miller wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM
A bit column is neither True nor False because bit is a numeric
datatype not a boolean. Usually 1 is understood to mean True and 0 is
understood to mean False. Of course it's always possible that the
person who designed your database may not have respected that
convention at all. One reason I dislike using the bit type is precisely
because of this ambiguity. A meaningful code makes it much clearer just
what the designer's intention is. On the other hand proper
documentation ought to answer your question in any case :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||They are what you want them to be; SQL server only stores the 0 or 1. In
Access, true is numerically represented by -1, and false is 0.
You could say that 1 is true and 0 is false, and extract data from the
column using "true" and "false" with a CASE expression:
SELECT
CASE
WHEN Column1 = 1 THEN 'True'
ELSE 'False'
END
FROM [Your Table]
"Michael Miller" wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||I think .Net automatically converts bit columns to 0=False and 1=True.
Outside of .Net it would be application specific. If you are going to use a
bit column, then I would make it a point to code around values of 0 and 1,
rather than True or False. You also need to take into account NULL values,
if this column allows them. BOL does not have much to say about this
datatype. Bottom line, bit is an integer value, and not a boolean value.
It is up to your code to determine how to use it. Here are some quotes from
BOL.
Converting bit Data
Converting to bit promotes any nonzero value to 1.
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft SQL ServerT optimizes the storage used for bit columns. If there
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on.
Special Data
Special data consists of data that does not fit any of the categories of
data such as binary data, character data, Unicode data, date and time data,
numeric data and monetary data.
Microsoft SQL ServerT 2000 includes four types of special data:
bit
Consists of either a 1 or a 0. Use the bit data type when representing TRUE
or FALSE, or YES or NO. For example, a client questionnaire that asks if
this is the client's first visit can be stored in a bit column.
Constants
A constant, also known as a literal or a scalar value, is a symbol that
represents a specific data value. The format of a constant depends on the
data type of the value it represents.
bit constants
bit constants are represented by the numbers zero or one, and are not
enclosed in quotation marks. If a number larger than one is used, it is
converted to one.
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> wrote in message
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||bit is an integer data type, and can be used to store 0, 1, or NULL.
You will need to define whether a value of 1 stands for true (or otherwise)
throughout your application.
- Data Types (SS2000 books online)
http://msdn.microsoft.com/library/d...br />
7msw.asp
Martin C K Poon
Senior Analyst Programmer
====================================
"Michael Miller" <MichaelMiller@.discussions.microsoft.com> bl
news:0CBE02FF-B292-4379-839A-BEF5ADDBE26A@.microsoft.com g...
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my
version
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||Thank you ALL.
I'll investigate and see what the intention of the creator was.
I'll start with 1=true/yes and 0=false/no
--
MichaelM
"Michael Miller" wrote:
> I have a field that I need to query for true and false.
> The field is a bit.
> Is True 0 or 1.
> I can't open Books Online and the download instructions based on my versio
n
> SQL 2000, are not clear and I don't know what detailed version I have nor
> where to find it.
> --
> MichaelM|||>> I have a field [sic] that I need to query for true and false.<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files. Totally different concepts!
You are missing another basic point here. There is no BOOLEAN data
type in SQL and it is a sign of bad programming to write with lower
level bit flags in a high-level language.
Get a book on RDBMS and read about three-valued logic and NULLs to
learn why this cannot be implemented in the SQL model.
Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?|||Understood. I am querying and reporting and needed to know how they are
using a particular field, which is bit. I didn't set up the database and
have differed with the contractor on other design matters as well. For
example, we have two types of tables, one called SAG and the other called
TRG. Instead of making a 3 char field, he set up one fieldname that called
TRG, and then he says whether it is true or not (the bit field). Since he
has 0's and 1's, I was trying to figure out which was which.
I would have named the field GridType, and entered either SAG or TRG, for
readability.
I understand about the education. I have no formal education in SQL Server,
other than some OJT working on a VB6 project with SQL, for about 3 years.
Added to that is 5 years in Access and 15 years in Paradox (DOS 1.0 -->Win
8.0).
Thanks for your input.
--
MichaelM
"--CELKO--" wrote:
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files. Totally different concepts!
>
> You are missing another basic point here. There is no BOOLEAN data
> type in SQL and it is a sign of bad programming to write with lower
> level bit flags in a high-level language.
> Get a book on RDBMS and read about three-valued logic and NULLs to
> learn why this cannot be implemented in the SQL model.
> Also, look at C# and VB -- do you use -1, +1, or 0 for BOOLEAN?
>
Boolean ?
There is no Boolean datatype.
Most people use a BIT (0 or 1).
There are arguments for and against using CHAR(1) values with a check
constraint ('Y', 'N'), ('T', 'F').
You're better off with either of those than a (var)char(5) allowing 'TRUE'
and 'FALSE' as strings.
Oh, and you will never be able to shorthand WHERE column = 1 for WHERE
column, or WHERE column = 0 for WHERE NOT column. Even though the datatype
you choose (along with the cnstraints if necessary) may seem like a valid
boolean expression to you, SQL Server does not consider them as such.
A
"Gabriel" <nospam@.nospam.com> wrote in message
news:uw2bPk2$GHA.3380@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Is there a way to define a boolean with SQL Server 2005 (Express)
> Thanks,
>
>> Btw, did you see the new error message 2005 gives you when you try to say[vbcol=seagreen]
Hehe, apparently there is some context that expects boolean type in SQL
Server !!!
Anith
|||>>> Btw, did you see the new error message 2005 gives you when you try to
> Hehe, apparently there is some context that expects boolean type in SQL
> Server !!!
Well, there are boolean expressions (not types), sort of, e.g.
WHERE EXISTS
(
...
)
|||Sorry, just wanted to clarify that I do get the joke. It's been a long day
here today.
|||>> It's been a long day here today.
Long Fridays, tell me about it :-)
Anith
Most people use a BIT (0 or 1).
There are arguments for and against using CHAR(1) values with a check
constraint ('Y', 'N'), ('T', 'F').
You're better off with either of those than a (var)char(5) allowing 'TRUE'
and 'FALSE' as strings.
Oh, and you will never be able to shorthand WHERE column = 1 for WHERE
column, or WHERE column = 0 for WHERE NOT column. Even though the datatype
you choose (along with the cnstraints if necessary) may seem like a valid
boolean expression to you, SQL Server does not consider them as such.
A
"Gabriel" <nospam@.nospam.com> wrote in message
news:uw2bPk2$GHA.3380@.TK2MSFTNGP04.phx.gbl...
> Hello,
> Is there a way to define a boolean with SQL Server 2005 (Express)
> Thanks,
>
>> Btw, did you see the new error message 2005 gives you when you try to say[vbcol=seagreen]
Hehe, apparently there is some context that expects boolean type in SQL
Server !!!
Anith
|||>>> Btw, did you see the new error message 2005 gives you when you try to
> Hehe, apparently there is some context that expects boolean type in SQL
> Server !!!
Well, there are boolean expressions (not types), sort of, e.g.
WHERE EXISTS
(
...
)
|||Sorry, just wanted to clarify that I do get the joke. It's been a long day
here today.
|||>> It's been a long day here today.
Long Fridays, tell me about it :-)
Anith
Tuesday, March 20, 2012
Booi.ini switch
Hello,
I read a lot of artical but still can't understand.
I have SQL 2005 ent edition 32BIT on Windows 2003 Ent R2 32 bit.
I have 4GB on my machine.
What switch I need ton the boot.ini
/3GB
/Userva=2900
/PAE (is this only if I have more than 4GB?)
Thanks
KBA http://support.microsoft.com/kb/274750 for more infomation.|||Thanks but this is for SQL 2000/7 and I need for SQL 2005,
Aslo I can't found there the /USERVA
|||Idanle,
You need to look at the bottom of the support link doc Satya gave to you...
|||PAE is useful for systems owning more than 4GB of RAM
/3GB limits the kernel space to just 1GB instead of 2GB to allow your applications to use up to 3GB instead of up to 2GB.
AWE is used by certain programs that support it (like SQLSRV05), usually along with PAE to provide your applications with more than 2GB of address space which is the norm.
Since your server is 4GB you should use /3GB if you believe the server won't slow down by limiting it to 1GB of kernel address space. PAE need not concern you for 4GB
|||Thanks a lot
so if I have SQL 2005 on 4GB rammachine I need to use
/3GB /AWE
|||If you want all your applications on the server (including SQL server) to use 3GB then use /3GB
If you want only SQL Server to use 3GB then use AWE
You're welcome
|||Dear JohDas,
my server runs with Windows 2003 small business server sp2 and we use SQL 2005 workgroup edition sp1. The physical memory is 3.5 Gbytes, paging file 2-4 Gbytes. Also the server has two Intel Xeon cpus. This is a dedicated SQL server.
We dont get any memory erros but we monitored a poor performance (user transactions take longer to finish), so we want to allow SQL to use more memory, we are trying to figure out the correct configuration. Do you think the following setup for boot.ini is correct and adequate:
/3Gb /userva=2560 /AWE
Regards,
ZindrosH
Hi ZindrosH,
the memory SQL Server reserves is different from what the OS keeps for itself. You could have 4 GB of RAM and have SQL Server using only 512MB of it due to bad configuration.
Use this query to see the configuration about the minimum and maximum memory that SQL Server uses:
SP_CONFIGURE ‘show advanced options’,1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
and change it accordingly to your needs
Cheers,
John
|||Dear John,
we haven't set any specific values to min &max server memory. We use the default values.
Because the box has more than 2 Gbytes (it has 3.5 Gbytes) memory and SQL 2005 Workgroup edition might use up to 3 Gbytes we would like to let SQL to use more memory. To my knowledge, one way, is to set specific values to boot.ini file. I am not pretty sure what these values should be.
May be this: /3Gb /userva=2560 /AWE
or
May be this: /3Gb /userva=2560 /PAE
or any other combination or option ?
Because the server is out to production we do not have the chance to play with these values.
Regards,
ZindrosH
|||
PAE is only required for SQL Server to use more than 4 GB of physical memory - you don't need it
Generally, for a standalone SQL Server, AWE is preferable to 3GB as it allows only SQL Server to take advantage of more than 2GB of RAM
The account used for SQL services will have to be given Windows "lock pages in memory" administrative credentials.
and then running this query to set AWE and max server memory:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 3072
RECONFIGURE
GO
You should always keep at least 1 GB of RAM for the OS
For more info check this KB article: 274750 and SQL books online
|||Dear John,
thank for your time and patience. I have read not only the links you provided but much more. Thats why I am confused....!
So, PAE is out.
Also since AWE is not supported for SQL 2005 Workgroup edition (I get a relative error when I try to set AWE from Management Studio), what remains is to use /3Gb and /userva=2560 to boot.ini. I guess, this setting will allow 2.5 Gbytes for all applications, including SQL and 1 Gbyte for OS.
What do you suggest, should I try this ?
ZindrosH
Booi.ini switch
Hello,
I read a lot of artical but still can't understand.
I have SQL 2005 ent edition 32BIT on Windows 2003 Ent R2 32 bit.
I have 4GB on my machine.
What switch I need ton the boot.ini
/3GB
/Userva=2900
/PAE (is this only if I have more than 4GB?)
Thanks
KBA http://support.microsoft.com/kb/274750 for more infomation.|||Thanks but this is for SQL 2000/7 and I need for SQL 2005,
Aslo I can't found there the /USERVA
|||Idanle,
You need to look at the bottom of the support link doc Satya gave to you...
|||PAE is useful for systems owning more than 4GB of RAM
/3GB limits the kernel space to just 1GB instead of 2GB to allow your applications to use up to 3GB instead of up to 2GB.
AWE is used by certain programs that support it (like SQLSRV05), usually along with PAE to provide your applications with more than 2GB of address space which is the norm.
Since your server is 4GB you should use /3GB if you believe the server won't slow down by limiting it to 1GB of kernel address space. PAE need not concern you for 4GB
|||Thanks a lot
so if I have SQL 2005 on 4GB rammachine I need to use
/3GB /AWE
|||If you want all your applications on the server (including SQL server) to use 3GB then use /3GB
If you want only SQL Server to use 3GB then use AWE
You're welcome
|||Dear JohDas,
my server runs with Windows 2003 small business server sp2 and we use SQL 2005 workgroup edition sp1. The physical memory is 3.5 Gbytes, paging file 2-4 Gbytes. Also the server has two Intel Xeon cpus. This is a dedicated SQL server.
We dont get any memory erros but we monitored a poor performance (user transactions take longer to finish), so we want to allow SQL to use more memory, we are trying to figure out the correct configuration. Do you think the following setup for boot.ini is correct and adequate:
/3Gb /userva=2560 /AWE
Regards,
ZindrosH
Hi ZindrosH,
the memory SQL Server reserves is different from what the OS keeps for itself. You could have 4 GB of RAM and have SQL Server using only 512MB of it due to bad configuration.
Use this query to see the configuration about the minimum and maximum memory that SQL Server uses:
SP_CONFIGURE ‘show advanced options’,1
GO
RECONFIGURE
GO
SP_CONFIGURE
GO
and change it accordingly to your needs
Cheers,
John
|||Dear John,
we haven't set any specific values to min &max server memory. We use the default values.
Because the box has more than 2 Gbytes (it has 3.5 Gbytes) memory and SQL 2005 Workgroup edition might use up to 3 Gbytes we would like to let SQL to use more memory. To my knowledge, one way, is to set specific values to boot.ini file. I am not pretty sure what these values should be.
May be this: /3Gb /userva=2560 /AWE
or
May be this: /3Gb /userva=2560 /PAE
or any other combination or option ?
Because the server is out to production we do not have the chance to play with these values.
Regards,
ZindrosH
|||
PAE is only required for SQL Server to use more than 4 GB of physical memory - you don't need it
Generally, for a standalone SQL Server, AWE is preferable to 3GB as it allows only SQL Server to take advantage of more than 2GB of RAM
The account used for SQL services will have to be given Windows "lock pages in memory" administrative credentials.
and then running this query to set AWE and max server memory:
sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 3072
RECONFIGURE
GO
You should always keep at least 1 GB of RAM for the OS
For more info check this KB article: 274750 and SQL books online
|||Dear John,
thank for your time and patience. I have read not only the links you provided but much more. Thats why I am confused....!
So, PAE is out.
Also since AWE is not supported for SQL 2005 Workgroup edition (I get a relative error when I try to set AWE from Management Studio), what remains is to use /3Gb and /userva=2560 to boot.ini. I guess, this setting will allow 2.5 Gbytes for all applications, including SQL and 1 Gbyte for OS.
What do you suggest, should I try this ?
ZindrosH
Thursday, March 8, 2012
Blocking
Can a Performance Alert be set up to detect blocking on a server? We do not get deadlocks but we do get a bit of blocking going on and it
would be nice to be notified when blocking is occurring.Originally posted by ToddBritt
Can a Performance Alert be set up to detect blocking on a server? We do not get deadlocks but we do get a bit of blocking going on and it
would be nice to be notified when blocking is occurring.
You can acheive this by using a combination of Scheduled Tasks, stored procedures and Alerts.
Write a stored procedure that generates a user-defined error message whenever there is blocking. Blocking can be checked by querying the sysprocesses system table.
Define a scheduled task that runs this stored procedure for every n minutes.
Define an Alert on the specific error message and configure it to send a mail.
would be nice to be notified when blocking is occurring.Originally posted by ToddBritt
Can a Performance Alert be set up to detect blocking on a server? We do not get deadlocks but we do get a bit of blocking going on and it
would be nice to be notified when blocking is occurring.
You can acheive this by using a combination of Scheduled Tasks, stored procedures and Alerts.
Write a stored procedure that generates a user-defined error message whenever there is blocking. Blocking can be checked by querying the sysprocesses system table.
Define a scheduled task that runs this stored procedure for every n minutes.
Define an Alert on the specific error message and configure it to send a mail.
Sunday, February 19, 2012
Blank Report after deployment
I'm having a bit of a problem with Report viewer but only after deployment. On my machine and various test machines (using IE6 and 7 and Firefox) all the reports in the program work fine.
However after we've deployed this project to a customer the reports are showing up as blank in report viewer. If we export them to pdf they show up in the pdf correctly.
We are using Cassini rather than IIS on the test system but we've installed this on a pc here to test and that seems to be working.
Anyone had similar issues? I know it could be many different things but I don't have a lot of info on the PC thats having problems.
Is this over ssl? What do you mean by blank?|||No its all running locally at this point so no SSL. When I say blank I mean the report Viewer is showing (and I can export to PDF/Excel which works fine) but I can't actually see the report report viewer. Its just a big white box under the report viewer toolbar.Sunday, February 12, 2012
Bitwise NOT Operation
I was looking into some of the System stored procedures like sp_denylogin, sp_grantLogin etc. I found bit wise operations like
update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
where name = @.loginame and srvid IS NULL
How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?
Look at the following sample code and result. I just wanted to understand how it works with int and smallint
declare @.a int
declare @.b smallint
declare @.c tinyint
select @.a = 1, @.b = 1, @.c = 1
select @.a=~@.a, @.b= ~@.b, @.c= ~@.c
select @.a, @.b, @.c
Result
~~~~~
-2 -2 254
Thanks in advance
GnanaInteresting question! In a former life, I worked on addressable converter interfaces, and much was done with bitmaps...so I played a bit also...
Note the results if you execute the following select in your example:
select @.a, @.b, @.c, cast(@.a AS varbinary), cast(@.b as varbinary), cast (@.c as varbinary)
Result
~~~~~
-2 -2 254 0xFFFFFFFE 0xFFFE 0xFE
Now you get to see the binary results...which still does not answer your question, but I betcha (as I head back to BOL) that the answer lies in there as the way the sign bit is interpreted in the larger two data types.
Betcha that the tinyint data type does not have a sign bit.
yep...that's it...from BOL:tinyint
Integer data from 0 through 255. Storage size is 1 byte.
So the issue is not so much that the bitwise operation actually WORKS any differently...what you are seeing is just the PRESENTATION differences between data types (the final select assumes you want the data interpreted in it's numeric value, not the bit-representation of the data in the variable...so you get a negative number because you have set the sign bit on with the bitwise operation you used on the larger data types (that have sign bits).
In other words, the bitwise operator works exactly the same on all three data types, it just LOOKS like it works differently when you select it back to see the results (without casting, that is).|||Nice explanation, Paul.|||Yeah, thanks! I learned something too (something I usually try to avoid at all costs)...though we don't use much bit manipulation here (at least not out where everyone can see ;) ).
update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
where name = @.loginame and srvid IS NULL
How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?
Look at the following sample code and result. I just wanted to understand how it works with int and smallint
declare @.a int
declare @.b smallint
declare @.c tinyint
select @.a = 1, @.b = 1, @.c = 1
select @.a=~@.a, @.b= ~@.b, @.c= ~@.c
select @.a, @.b, @.c
Result
~~~~~
-2 -2 254
Thanks in advance
GnanaInteresting question! In a former life, I worked on addressable converter interfaces, and much was done with bitmaps...so I played a bit also...
Note the results if you execute the following select in your example:
select @.a, @.b, @.c, cast(@.a AS varbinary), cast(@.b as varbinary), cast (@.c as varbinary)
Result
~~~~~
-2 -2 254 0xFFFFFFFE 0xFFFE 0xFE
Now you get to see the binary results...which still does not answer your question, but I betcha (as I head back to BOL) that the answer lies in there as the way the sign bit is interpreted in the larger two data types.
Betcha that the tinyint data type does not have a sign bit.
yep...that's it...from BOL:tinyint
Integer data from 0 through 255. Storage size is 1 byte.
So the issue is not so much that the bitwise operation actually WORKS any differently...what you are seeing is just the PRESENTATION differences between data types (the final select assumes you want the data interpreted in it's numeric value, not the bit-representation of the data in the variable...so you get a negative number because you have set the sign bit on with the bitwise operation you used on the larger data types (that have sign bits).
In other words, the bitwise operator works exactly the same on all three data types, it just LOOKS like it works differently when you select it back to see the results (without casting, that is).|||Nice explanation, Paul.|||Yeah, thanks! I learned something too (something I usually try to avoid at all costs)...though we don't use much bit manipulation here (at least not out where everyone can see ;) ).
Bits of Indexes RFC
I suppose this is more an RFC than a true question, but I've come to realize "bit" data types do not optimize a query when they are used in a WHERE clause. Since they cannot be indexed, it forces the analyzer to query an available index for rows based upon the non-bit constraints. The result is then scanned to match the values of the bit constraints of the query. So you're potentially doing a full table scan, or an index scan.
My original thought was that I'd improve performance by using bit fields since they are a smaller datatype, but as development progressed, we began using those bits in our queries. At this point, it may be beneficial to convert those bit types to smallint.
Am I wrong in my conclusion?Nope...
USE Northwind
GO
CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO
CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO
--[CTRL]+k
SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'
DROP TABLE myTable99
GO|||WOW! Learn something new every day! Now I've got to go and start changing all my bit fields to something else. The execution plan in QA showed the bit field about three times slower for a simple select!
Thanks Brett... Keep the info coming..|||...It also produced table scan. I noticed though that it does not behave the same way if bit field is part of a composit index, even if it is the first field in the index field list.|||I get Index Scans...I have no idea why it would decide to use the new indexes and do the scan...could be because there's no data..
Hell the last 2 indexes are the same (if not larger) in...
But why would a 3 selects use each new index as I add them?
USE Northwind
GO
CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO
CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO
--[CTRL]+k
SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'
CREATE INDEX myTable99_IX4 ON myTable99(Col2,Col1,Col3)
GO
SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'
CREATE INDEX myTable99_IX5 ON myTable99(Col1,Col3,Col2)
GO
SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'
DROP TABLE myTable99
GO|||Right.. I suppose then that bits should be relegated to read/write only.. not query constraints...
So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.
In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)|||So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.
It's probably due to the cardinality...bit is 0 or 1...anything with a low number of different values...will not make a good index...it will always cause a scan..
In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)
In a table..no...in an index, it's imperative...at least that's what I've always believed...
For example...an index with Col3, Col2, Col1
And a predeicate of Col1 = something
Is a table scan...ever read up on index intersection?
Still don't know why my example (besides probably be a bad one) shows the index scan using IX4 and IX5...got a very nice seek using IX1 thru IX3
Wonder why the optimizer chose them...
I'm (as usual, so it's not disconcerting) perplexed...
My original thought was that I'd improve performance by using bit fields since they are a smaller datatype, but as development progressed, we began using those bits in our queries. At this point, it may be beneficial to convert those bit types to smallint.
Am I wrong in my conclusion?Nope...
USE Northwind
GO
CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO
CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO
--[CTRL]+k
SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'
DROP TABLE myTable99
GO|||WOW! Learn something new every day! Now I've got to go and start changing all my bit fields to something else. The execution plan in QA showed the bit field about three times slower for a simple select!
Thanks Brett... Keep the info coming..|||...It also produced table scan. I noticed though that it does not behave the same way if bit field is part of a composit index, even if it is the first field in the index field list.|||I get Index Scans...I have no idea why it would decide to use the new indexes and do the scan...could be because there's no data..
Hell the last 2 indexes are the same (if not larger) in...
But why would a 3 selects use each new index as I add them?
USE Northwind
GO
CREATE TABLE myTable99(Col1 smallint, Col2 bit, Col3 Char(1))
GO
CREATE INDEX myTable99_IX1 ON myTable99(Col1)
CREATE INDEX myTable99_IX2 ON myTable99(Col2)
CREATE INDEX myTable99_IX3 ON myTable99(Col3)
GO
--[CTRL]+k
SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'
CREATE INDEX myTable99_IX4 ON myTable99(Col2,Col1,Col3)
GO
SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'
CREATE INDEX myTable99_IX5 ON myTable99(Col1,Col3,Col2)
GO
SELECT * FROM myTable99 WHERE Col1 = 0
SELECT * FROM myTable99 WHERE Col2 = 0
SELECT * FROM myTable99 WHERE Col3 = 'x'
DROP TABLE myTable99
GO|||Right.. I suppose then that bits should be relegated to read/write only.. not query constraints...
So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.
In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)|||So, why aren't bits indexable? I understand some file types cannot be indexed, but a bit is a bit. A tinyint is still 8 bits.
It's probably due to the cardinality...bit is 0 or 1...anything with a low number of different values...will not make a good index...it will always cause a scan..
In the index, does the order of the columns in your table, index, and result set even matter? (that question might be an entirely separate subject, and irrelevant to the original post)
In a table..no...in an index, it's imperative...at least that's what I've always believed...
For example...an index with Col3, Col2, Col1
And a predeicate of Col1 = something
Is a table scan...ever read up on index intersection?
Still don't know why my example (besides probably be a bad one) shows the index scan using IX4 and IX5...got a very nice seek using IX1 thru IX3
Wonder why the optimizer chose them...
I'm (as usual, so it's not disconcerting) perplexed...
Friday, February 10, 2012
Bit vs. Varchar?
Saw a friend of mine using a varchar field like a bit field (only storing 1
character). I told him that it would be better to change that to a bit
field, and he said, Why? What is the difference between Varchar(1) and Bit?
Thanks,
DrewDrew wrote:
> Saw a friend of mine using a varchar field like a bit field (only
> storing 1 character). I told him that it would be better to change
> that to a bit field, and he said, Why? What is the difference
> between Varchar(1) and Bit?
> Thanks,
> Drew
There is no ANSI standard for a bit column. It's a SQL Server invention.
Bit columns only take up 1 bit of storage (giving you the ability to
store as many as 8 bit columns in a single byte). I personally don't
mind them, but there are arguments for using a char(1) with a check
contraints for Y/N, 0/1, etc.
I would not use a varchar because of the added overhead. Use a char(1)
or a bit.
David Gugick
Imceda Software
www.imceda.com|||A bit can store only 0 or 1.
If storing just a single character and it will never be null best to
use char(1) instead of varchar(1). The reason is less data space is
used. Same reason applies to bit.
Tim S|||Correction bit can also store NULL.|||I prefer to use a TINYINT for this. It only takes up one byte just like a
bit or char(1) but an hold up to 255 values even if you only want to use 0
or 1. It's cleaner than a bit and more versatile.
Andrew J. Kelly SQL MVP
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uZ5UFvFPFHA.3880@.tk2msftngp13.phx.gbl...
> Saw a friend of mine using a varchar field like a bit field (only storing
> 1 character). I told him that it would be better to change that to a bit
> field, and he said, Why? What is the difference between Varchar(1) and
> Bit?
> Thanks,
> Drew
>|||Thanks for everyone's reply.
Thanks,
Drew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OKaeVLGPFHA.2348@.tk2msftngp13.phx.gbl...
>I prefer to use a TINYINT for this. It only takes up one byte just like a
>bit or char(1) but an hold up to 255 values even if you only want to use 0
>or 1. It's cleaner than a bit and more versatile.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:uZ5UFvFPFHA.3880@.tk2msftngp13.phx.gbl...
>|||>> Saw a friend of mine using a varchar field [sic] like a bit field
[sic] (only storing 1
character). I told him that it would be better to change that to a bit
field [sic] , and he said, Why? What is the difference between
Varchar(1) and Bit? <<
Rows are not records; columns are not fields and BIT is not a Standard
SQL datatype. in fact, it makes no sense in a high level language.
That is what assembly language programmers use in the hardware and not
what SQL programmers have in an abstract darta model. VARCHAR(1) also
makes no sense; use CHAR(1) instead.
What a good SQL porgrammers does is design encoding schemes. We do not
write code which depends on flags. And it is hard to design encoding
schemes.|||>> Correction bit can also store NULL. <<
No, bit can be only one or zero; look it up in any computer science
book. On the other hand BIT, the proprietary datatype in SQL Server,
can store 1, 0 or NULL. Kinda screws up portability, don't it?|||>> I prefer to use a TINYINT for this. <<
Another proprietary datatype, but at least it has room for more codes
and can port to INTEGER.|||I think I have my answer. The spec simply calls for smallint
to be smaller than int. I guess I still don't see why they
didn't add a further designation that stated that tinyint
was smaller than smallint. It's already the case that
without knowing the given implementation's bounds on int and
smallint, you can't logically decide when to use one over
the other.
Thomas
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:utGU6aHPFHA.2132@.TK2MSFTNGP14.phx.gbl...
> That's curious. What is the logic behind having a 2-byte
> integer (smallint) and a 4-byte integer (integer) but not
> a 1-byte integer (SQL's tinyint)?
> Thomas
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1112987344.909087.50090@.g14g2000cwa.googlegroups.com...
>
character). I told him that it would be better to change that to a bit
field, and he said, Why? What is the difference between Varchar(1) and Bit?
Thanks,
DrewDrew wrote:
> Saw a friend of mine using a varchar field like a bit field (only
> storing 1 character). I told him that it would be better to change
> that to a bit field, and he said, Why? What is the difference
> between Varchar(1) and Bit?
> Thanks,
> Drew
There is no ANSI standard for a bit column. It's a SQL Server invention.
Bit columns only take up 1 bit of storage (giving you the ability to
store as many as 8 bit columns in a single byte). I personally don't
mind them, but there are arguments for using a char(1) with a check
contraints for Y/N, 0/1, etc.
I would not use a varchar because of the added overhead. Use a char(1)
or a bit.
David Gugick
Imceda Software
www.imceda.com|||A bit can store only 0 or 1.
If storing just a single character and it will never be null best to
use char(1) instead of varchar(1). The reason is less data space is
used. Same reason applies to bit.
Tim S|||Correction bit can also store NULL.|||I prefer to use a TINYINT for this. It only takes up one byte just like a
bit or char(1) but an hold up to 255 values even if you only want to use 0
or 1. It's cleaner than a bit and more versatile.
Andrew J. Kelly SQL MVP
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uZ5UFvFPFHA.3880@.tk2msftngp13.phx.gbl...
> Saw a friend of mine using a varchar field like a bit field (only storing
> 1 character). I told him that it would be better to change that to a bit
> field, and he said, Why? What is the difference between Varchar(1) and
> Bit?
> Thanks,
> Drew
>|||Thanks for everyone's reply.
Thanks,
Drew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OKaeVLGPFHA.2348@.tk2msftngp13.phx.gbl...
>I prefer to use a TINYINT for this. It only takes up one byte just like a
>bit or char(1) but an hold up to 255 values even if you only want to use 0
>or 1. It's cleaner than a bit and more versatile.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:uZ5UFvFPFHA.3880@.tk2msftngp13.phx.gbl...
>|||>> Saw a friend of mine using a varchar field [sic] like a bit field
[sic] (only storing 1
character). I told him that it would be better to change that to a bit
field [sic] , and he said, Why? What is the difference between
Varchar(1) and Bit? <<
Rows are not records; columns are not fields and BIT is not a Standard
SQL datatype. in fact, it makes no sense in a high level language.
That is what assembly language programmers use in the hardware and not
what SQL programmers have in an abstract darta model. VARCHAR(1) also
makes no sense; use CHAR(1) instead.
What a good SQL porgrammers does is design encoding schemes. We do not
write code which depends on flags. And it is hard to design encoding
schemes.|||>> Correction bit can also store NULL. <<
No, bit can be only one or zero; look it up in any computer science
book. On the other hand BIT, the proprietary datatype in SQL Server,
can store 1, 0 or NULL. Kinda screws up portability, don't it?|||>> I prefer to use a TINYINT for this. <<
Another proprietary datatype, but at least it has room for more codes
and can port to INTEGER.|||I think I have my answer. The spec simply calls for smallint
to be smaller than int. I guess I still don't see why they
didn't add a further designation that stated that tinyint
was smaller than smallint. It's already the case that
without knowing the given implementation's bounds on int and
smallint, you can't logically decide when to use one over
the other.
Thomas
"Thomas" <replyingroup@.anywhere.com> wrote in message
news:utGU6aHPFHA.2132@.TK2MSFTNGP14.phx.gbl...
> That's curious. What is the logic behind having a 2-byte
> integer (smallint) and a 4-byte integer (integer) but not
> a 1-byte integer (SQL's tinyint)?
> Thomas
>
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1112987344.909087.50090@.g14g2000cwa.googlegroups.com...
>
bit value as parameter
Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
@.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,ran
ge90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,ran
ge100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70
,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80
,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90
,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range10
0)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
> @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,r
ange90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,r
ange100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70
,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80
,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90
,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range10
0)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:OTlfCgtfGHA.4880@.TK2MSFTNGP03
.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
@.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,ran
ge90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,ran
ge100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70
,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80
,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90
,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range10
0)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn =
> @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40
,range50,range60,range70,range80,r
ange90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50
,range60,range70,range80,range90,r
ange100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60
,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70
,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80
,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90
,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range10
0)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '-TpO')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> glsD:OTlfCgtfGHA.4880@.TK2MSFTNGP03
.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>
bit value as parameter
Hi,
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn = @.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn => @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:OTlfCgtfGHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have a Store Procedure and include a bit parameter need to pass, such
>> as:
>> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
>> But I don't know how to pass the Bit Value,
>> I try to use 0, 1, TRUE, FALSE, the error message always response:
>> Error converting data type varchar to bit.
>> Someone can give me a favor, Thanks!
>> Angi
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Store Procedure as follow:
>> CREATE PROCEDURE spGenerateL1
>> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
>> @.Chart AS NVARCHAR(30)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.tKpiName AS NVARCHAR(40)
>> DECLARE @.tUnitType AS NVARCHAR( 1)
>> DECLARE @.tCurrentValue AS DECIMAL(7,2)
>> DECLARE @.tRange AS BINARY
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> DECLARE spCursor CURSOR FOR
>> SELECT a.KpiName, a.UnitType, b.CurrentValue
>> FROM BisKpi a INNER JOIN BisKpiDetail b
>> ON a.KpiGUID = b.KpiGUID
>> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn
>> = @.Orgn
>> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
>> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
>> OPEN spCursor
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.tRange = (SELECT CASE
>> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
>> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
>> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
>> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
>> COALESCE(range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
>> COALESCE(range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
>> COALESCE(range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
>> COALESCE(range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
>> COALESCE(range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
>> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
>> ELSE range0
>> END AS Indicator
>> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
>> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> END
>> CLOSE spCursor
>> DEALLOCATE spCursor
>
I have a Store Procedure and include a bit parameter need to pass, such as:
exec spGenerateL1 'Bu01', Bit Value,'Administrator'
But I don't know how to pass the Bit Value,
I try to use 0, 1, TRUE, FALSE, the error message always response:
Error converting data type varchar to bit.
Someone can give me a favor, Thanks!
Angi
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Store Procedure as follow:
CREATE PROCEDURE spGenerateL1
@.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
@.Chart AS NVARCHAR(30)
AS
SET NOCOUNT ON
DECLARE @.tKpiName AS NVARCHAR(40)
DECLARE @.tUnitType AS NVARCHAR( 1)
DECLARE @.tCurrentValue AS DECIMAL(7,2)
DECLARE @.tRange AS BINARY
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE spCursor CURSOR FOR
SELECT a.KpiName, a.UnitType, b.CurrentValue
FROM BisKpi a INNER JOIN BisKpiDetail b
ON a.KpiGUID = b.KpiGUID
AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn = @.Orgn
AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
DATEDIFF(DAY, FullDate, GETDATE()) = 0)
OPEN spCursor
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.tRange = (SELECT CASE
WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
COALESCE(range40,range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
COALESCE(range50,range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
COALESCE(range60,range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
COALESCE(range70,range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
COALESCE(range80,range90,range100)
WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
ELSE range0
END AS Indicator
FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
END
CLOSE spCursor
DEALLOCATE spCursorIn your table "BisKpi", what data type is the field "KpuUserMode"
defined as?|||Hi, Tracy,
The field's data type is Bit.
Angi
"Tracy McKibben" <tracy.mckibben@.gmail.com>
'?:1148386860.513912.28450@.y43g2000cwc.googlegroups.com...
> In your table "BisKpi", what data type is the field "KpuUserMode"
> defined as?
>|||Hi Angi
Try this:
declare @.mybit bit
set @.mybit = 1 -- or set @.mybit = 0
exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"angi" <angi@.news.microsoft.com> wrote in message
news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a Store Procedure and include a bit parameter need to pass, such
> as:
> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
> But I don't know how to pass the Bit Value,
> I try to use 0, 1, TRUE, FALSE, the error message always response:
> Error converting data type varchar to bit.
> Someone can give me a favor, Thanks!
> Angi
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Store Procedure as follow:
> CREATE PROCEDURE spGenerateL1
> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
> @.Chart AS NVARCHAR(30)
> AS
> SET NOCOUNT ON
> DECLARE @.tKpiName AS NVARCHAR(40)
> DECLARE @.tUnitType AS NVARCHAR( 1)
> DECLARE @.tCurrentValue AS DECIMAL(7,2)
> DECLARE @.tRange AS BINARY
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> DECLARE spCursor CURSOR FOR
> SELECT a.KpiName, a.UnitType, b.CurrentValue
> FROM BisKpi a INNER JOIN BisKpiDetail b
> ON a.KpiGUID = b.KpiGUID
> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn => @.Orgn
> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
> OPEN spCursor
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.tRange = (SELECT CASE
> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
> COALESCE(range40,range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
> COALESCE(range50,range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
> COALESCE(range60,range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
> COALESCE(range70,range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
> COALESCE(range80,range90,range100)
> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
> ELSE range0
> END AS Indicator
> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
> END
> CLOSE spCursor
> DEALLOCATE spCursor
>|||Kalen,
Thank u very much!
Thanks for Tracy, too.
For this point, if Bit field needs to implement such way to pass parameter,
it seems inconvenient and let me want to change it's data type as char or
something else to be a flag.
So, any good idea about use Bit as a flag? or what's kind of data type is
better to be a flag?
Thanks!
Angi
"Kalen Delaney" <replies@.public_newsgroups.com> ¼¶¼g©ó¶l¥ó·s»D:OTlfCgtfGHA.4880@.TK2MSFTNGP03.phx.gbl...
> Hi Angi
> Try this:
> declare @.mybit bit
> set @.mybit = 1 -- or set @.mybit = 0
> exec spGenerateL1 'Bu01', @.mybit ,'Administrator'
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:%23Hb6UdkfGHA.3916@.TK2MSFTNGP04.phx.gbl...
>> Hi,
>> I have a Store Procedure and include a bit parameter need to pass, such
>> as:
>> exec spGenerateL1 'Bu01', Bit Value,'Administrator'
>> But I don't know how to pass the Bit Value,
>> I try to use 0, 1, TRUE, FALSE, the error message always response:
>> Error converting data type varchar to bit.
>> Someone can give me a favor, Thanks!
>> Angi
>> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>> Store Procedure as follow:
>> CREATE PROCEDURE spGenerateL1
>> @.Category AS NVARCHAR(10), @.KpiUserMode AS BIT, @.Orgn AS NVARCHAR(15),
>> @.Chart AS NVARCHAR(30)
>> AS
>> SET NOCOUNT ON
>> DECLARE @.tKpiName AS NVARCHAR(40)
>> DECLARE @.tUnitType AS NVARCHAR( 1)
>> DECLARE @.tCurrentValue AS DECIMAL(7,2)
>> DECLARE @.tRange AS BINARY
>> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
>> DECLARE spCursor CURSOR FOR
>> SELECT a.KpiName, a.UnitType, b.CurrentValue
>> FROM BisKpi a INNER JOIN BisKpiDetail b
>> ON a.KpiGUID = b.KpiGUID
>> AND a.Category = @.Category AND a.KpiUserMode = @.KpiUserMode AND b.Orgn
>> = @.Orgn
>> AND b.StartDate = (SELECT FirstDateOfWeek FROM BisCalendar WHERE
>> DATEDIFF(DAY, FullDate, GETDATE()) = 0)
>> OPEN spCursor
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> WHILE @.@.FETCH_STATUS = 0
>> BEGIN
>> SET @.tRange = (SELECT CASE
>> WHEN @.tCurrentValue BETWEEN 1 AND 10 THEN
>> COALESCE(range10,range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 10 AND 20 THEN
>> COALESCE(range20,range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 20 AND 30 THEN
>> COALESCE(range30,range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 30 AND 40 THEN
>> COALESCE(range40,range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 40 AND 50 THEN
>> COALESCE(range50,range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 50 AND 60 THEN
>> COALESCE(range60,range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 60 AND 70 THEN
>> COALESCE(range70,range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 70 AND 80 THEN
>> COALESCE(range80,range90,range100)
>> WHEN @.tCurrentValue BETWEEN 80 AND 90 THEN COALESCE(range90,range100)
>> WHEN @.tCurrentValue BETWEEN 90 AND 100 THEN range100
>> ELSE range0
>> END AS Indicator
>> FROM Bis.DBO.ChartConfig WHERE ChartName = '¾î-¤T¤p¬õºñ¿O')
>> SELECT @.tKpiName, @.tUnitType, @.tCurrentValue, @.tRange
>> FETCH NEXT FROM spCursor INTO @.tKpiName, @.tUnitType, @.tCurrentValue
>> END
>> CLOSE spCursor
>> DEALLOCATE spCursor
>
Bit Value
Hi there, I'm not sure if this the appropriate group so apologies if
it lies outside the boundary.
it lies outside the boundary.
Senario: I have a customer table with contains a bunch of different
bit values that represent true/false values pertaining to the
customer. I decided for the purpose of clarity I would move these
values into another table. This way I would keep the customer details
(name, age, etc) separate from these values.
Question: Is this a good idea? Or could I somehow tally up all these
bit values and store it in one field in the customer table?
The application is a website and it is built using ASP.NET (VB.NET)
Any opinions would be great.
Cheers,
JackOn 18 Jan 2005 17:06:28 -0800, Jack wrote:
>Hi there, I'm not sure if this the appropriate group so apologies if
>it lies outside the boundary.
>Senario: I have a customer table with contains a bunch of different
>bit values that represent true/false values pertaining to the
>customer. I decided for the purpose of clarity I would move these
>values into another table. This way I would keep the customer details
>(name, age, etc) separate from these values.
>Question: Is this a good idea? Or could I somehow tally up all these
>bit values and store it in one field in the customer table?
>The application is a website and it is built using ASP.NET (VB.NET)
>Any opinions would be great.
Hi Jack,
I'd say: replace the bit columns with somewhat more descriptive columns,
like
ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))
I don't see any advantage in moving these columns to a seperate table,
unless you are approaching the max number of columns per table (quite
unlikely) or unless you need to store these yes/no values only for a
limited subset of your customers.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:r85su0peh2op7lhf1i7eocfdm2shms59vb@.4ax.com...
> On 18 Jan 2005 17:06:28 -0800, Jack wrote:
>>Hi there, I'm not sure if this the appropriate group so apologies if
>>it lies outside the boundary.
>>
>>Senario: I have a customer table with contains a bunch of different
>>bit values that represent true/false values pertaining to the
>>customer. I decided for the purpose of clarity I would move these
>>values into another table. This way I would keep the customer details
>>(name, age, etc) separate from these values.
>>
>>Question: Is this a good idea? Or could I somehow tally up all these
>>bit values and store it in one field in the customer table?
>>
>>The application is a website and it is built using ASP.NET (VB.NET)
>>
>>Any opinions would be great.
> Hi Jack,
> I'd say: replace the bit columns with somewhat more descriptive columns,
> like
> ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))
> I don't see any advantage in moving these columns to a seperate table,
> unless you are approaching the max number of columns per table (quite
> unlikely) or unless you need to store these yes/no values only for a
> limited subset of your customers.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
Another possible beneficial thing you can do with a 'side-car' table like
this.
Save only one row per distinct combonation of flags and store the integer
key to that bitmap pattern in the main table.
With an indexed column you would be able to more quickly select on those
values.
FamilyType Table
-----
FamilyType Married BothWork HaveKids Descripiotn
--------------------
1 Y Y N DINK
2 Y N Y That Type
3 N Y N Whatever
etc...
customer Table
----
CustomerKey Name FamilyType
----------
1 joe and linda 1
2 bob and rob 3
3 karl and cindy 2
You can use a view, a proc, or an instead of trigger to make sure that
instead of updating the flags, you simply select the correct family type
code.|||Hugo Kornelis (hugo@.pe_NO_rFact.in_SPAM_fo) writes:
> I'd say: replace the bit columns with somewhat more descriptive columns,
> like
> ColName CHAR(1) NOT NULL CHECK (ColName IN ('Y', 'N'))
What can be more descriptive that 0 and 1?
Using character values when there are perfectly usable numeric values
as there are for binary value is asking for trouble. Let's see was
Y/N, T/F, J/N or something else?
We once had a lot of such columns in our database, but almost all are
bit columns these days. And, no, while the name of the type is ab_yesno,
the values are not Y/N, but J/N.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Jack (jack-b@.humlog.com) writes:
> Hi there, I'm not sure if this the appropriate group so apologies if
> it lies outside the boundary.
> Senario: I have a customer table with contains a bunch of different
> bit values that represent true/false values pertaining to the
> customer. I decided for the purpose of clarity I would move these
> values into another table. This way I would keep the customer details
> (name, age, etc) separate from these values.
> Question: Is this a good idea? Or could I somehow tally up all these
> bit values and store it in one field in the customer table?
The last thing would be a bad idea. That's more cumbersome to use, and
more prone to errors.
As for shuffling the bit columns to another table, well, it depends. We
actually did this with our accounts table, since we found that we were
costantly adding bit columns to it, of which some were for tiny marginal
features that were only referred to in one or two places.
Therefore we move these less used bit columns to this table:
CREATE TABLE accountflags (accountno int NOT NULL,
flag varchar(15) NOT NULL,
CONSTRAINT pk_flags PRIMARY KEY (accountno, flag))
That is, the bit column became rows. And a flag is set for an account
if there is a row in the table, else not. The flag themselves are
defined in another table, one that is loaded with data from scripts
when the database is created.
But if all you want to do is move bit columns to a side table, but
keep them as columns, I'm not sure that it's worth the effort.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||machine level things like a BIT or BYTE datatype have no place in a
high level language like SQL. SQL is a high level language; it is
abstract and defined without regard to PHYSICAL implementation. This
basic principle of data modeling is called data abstraction.
SQL is a high level language; it is abstract and defined without regard
to PHYSICAL implementation. This basic principle of data modeling is
called data abstraction.
Bits and Bytes are the <i>lowest<i> units of hardware-specific,
physical implementation you can get. Are you on a high-end or low-end
machine? Does the machine have 8, 16, 32, 64, or 128 bit words? Twos
complement or ones complement math? Hey, the standards allow decimal
machines, so bits do not exist at all! What about NULLs? To be a SQL
datatype, you have to have NULLs, so what is a NULL bit? By definition
a bit, is on or off and has no NULL. If you vendor adds NULLs to bit,
how are the bit-wise operations defined? Oh what a tangled web we
weave when first we mix logical and physical :)
What does the implementation of the host languages do with bits? Did
you know that +1, +0, -0 and -1 are all used for BOOLEANs, but not
consistently? In C#, Boolean values are 0/1 for FALSE/TRUE, while
VB.NET has boolean values of 0/-1 for FALSE/TRUE and they are
proprietary languages from the same vendor. That means <i>all<i> the
host languages -- present, future and not-yet-defined -- can be
different. Surely, no good programmer would ever write non-portable
code by getting to such a low level as bit fiddling!!
There are usually two situations in practice. Either the bits are
individual attributes or they are used as a vector to represent a
single attribute. In the case of a single attribute, the encoding is
limited to two values, which do not port to host languages or other
SQLs, cannot be easily understood by an end user, and which cannot be
expanded.
In the second case what some Newbies, who are still thinking in terms
of second and third generation programming languages or even punch
cards, do is build a vector for a series of "yes/no" status codes,
failing to see the status vector as a single attribute. Did you ever
play the children's game "20 Questions" when you were young?
Imagine you have six components for a loan approval, so you allocate
bits in your second generation model of the world. You have 64 possible
vectors, but only 5 of them are valid (i.e. you cannot be rejected for
bankruptcy and still have good credit). For your data integrity, you
can:
1) Ignore the problem. This is actually what <i>most<i> newbies do.
2) Write elaborate CHECK() constraints with user defined functions or
proprietary bit level library functions that cannot port and that run
like cold glue.
Now we add a 7-th condition to the vector -- which end does it go on?
Why? How did you get it in the right place on all the possible
hardware that it will ever use? Did all the code that references a bit
in a word by its position do it right after the change?
You need to sit down and think about how to design an encoding of the
data that is high level, general enough to expand, abstract and
portable. For example, is that loan approval a hierarchical code?
concatenation code? vector code? etc? Did you provide codes for
unknown, missing and N/A values? It is not easy to design such things!|||--CELKO-- (jcelko212@.earthlink.net) writes:
> machine level things like a BIT or BYTE datatype have no place in a
> high level language like SQL. SQL is a high level language; it is
> abstract and defined without regard to PHYSICAL implementation. This
> basic principle of data modeling is called data abstraction.
Completely wrong. In some language you can say "String" and the language
will allocate for you. In SQL Server you have to specify the max length
of any character column you want to use, and you cannot have more than
8000 bytes on a page. (There is varchar(MAX) in SQL 2005, but practice
will remain to specify an upper bound.)
As a further example, consider the whole range of tinyint, smallint,
int and bigint.
Since you store data on disk, the actual storage format matters a whole
lot to database programmers. Too much storage has bad effect on performance.
And of course, as datatype, BIT has nothing machine-level at all. It is
just a name for something that can have two values. While it could have
been called Boolean, Celko, Pamela or Urban, the name bit is quite good,
because most people have an understanding of how much information you
can cram into a bit.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> Surely, no good programmer would ever write non-portable
> code by getting to such a low level as bit fiddling!!
There's nothing wrong with fiddling with your bits is there ? :-)
Bit type parameter for stored procedure
I am trying to supply a bit type parameter to a stored procedure. This is used to update a Bit type field in a table. The field is called PDI
Please please please always post the error encountered, not just the fact that you have an error.
The syntax I am trying to use is:
MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = -1
When I do my ExecuteNonQuery I get error 8114
What am I doing wrong?The bit data type can hold one of the following values: 1, 0, or NULL. You cannot store a -1 in a bit column.
Terri|||Ok thanks for that. It will indeed accept 1 or 0
I now need to use the value from a CheckBox type control on my Form. So now
My Code reads:
MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = pdi.value
This generates the Error: "String was not recognised as a valid boolean."
What is the syntax for casting to a bit type value?|||You will likely need to convert your string value to a boolean value, using the ToBoolean method, something like this:
MyStoredProcedure.Parameters("@.pdi").value = System.Convert.ToBoolean(pdi.value)
See this article with handy chart for reference:Accessing SQL Server Data in C# with ADO.NET.
Terri|||No that didn't work either.
Just for the hell of it I decided to put the value of the PDI control into a span control like this:
message.innerhtml=pdi.value
I expected to see True or False or 1 or 0 or something. What I got was S00817
What is that!! I'm obviosly going the wrong way about extracting the value from a CheckBox control|||We might need to see more of your code.
Are you using a CheckBox or a CheckBoxList?
If a CheckBox, then it would probably be pdi.Checked, which already returns a boolean value.
I am sorry to be giving you the answer in pieces.
Terri|||Hi,
You need to use:
MyStoredProcedure.Parameters.Add(New SqlParameter("@.Pdi",SqlDbtype.bit))
MyStoredProcedure.Parameters("@.pdi").value = pdi.checked
or unchecked.
HTH
JB|||Yes that fixed it.
MyStoredProcedure.Parameters("@.pdi").value = pdi.checked
Thanks for your help|||
my check box is in the datagrid. I used this line. it have error.
comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked
Here is my html code. I tried to assign the check box value to byte. It did not work, too. Thanks.
<asp:datagrid id="dtgPhysician" style="Z-INDEX: 102; LEFT: 8px; POSITION: absolute; TOP: 88px" runat="server" Font-Names="Arial" OnCancelCommand="dtgPhysician_Cancel" OnEditCommand="dtgPhysician_Edit" Width="985px" AutoGenerateColumns="False" PagerStyle-Mode="NumericPages" DataKeyField="License" BorderColor="#3366CC" BorderStyle="None" BorderWidth="1px" CellPadding="4" ShowHeader="true" EditItemStyle-BackColor="#eeeeee" BackColor="White" OnUpdateCommand="dtgPhysician_Update">
<SelectedItemStyle Font-Bold="True" ForeColor="#CCFF99" BackColor="#009999"></SelectedItemStyle>
<EditItemStyle BackColor="#EEEEEE"></EditItemStyle>
<AlternatingItemStyle BorderColor="#C0C0FF"></AlternatingItemStyle>
<ItemStyle ForeColor="#003399" BackColor="White"></ItemStyle>
<HeaderStyle Font-Size="Medium" Font-Names="Arial Black" Font-Bold="True" HorizontalAlign="Center" ForeColor="#CCCCFF" VerticalAlign="Middle" BackColor="#003399"></HeaderStyle>
<FooterStyle ForeColor="#003399" BackColor="#99CCCC"></FooterStyle>
<Columns>
<asp:EditCommandColumn ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" EditText="Edit Info"></asp:EditCommandColumn>
<asp:BoundColumn DataField="PName" SortExpression="PNAME asc" ReadOnly="True" HeaderText="Name" HeaderStyle-Font-Name="arial">
<HeaderStyle Font-Names="arial"></HeaderStyle>
</asp:BoundColumn>
<asp:TemplateColumn HeaderText="Status">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Status") %>
</ItemTemplate>
<EditItemTemplate>
<asp:DropDownList runat="server" id="lstStatus" DataTextField="Status" SelectedIndex ='<%# GetSelIndex(Container.DataItem("Status")) %>'>
<asp:ListItem Value="0">DOC</asp:ListItem>
<asp:ListItem Value="1">POE</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Group">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "PGroup") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtGroup" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "PGroup") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Email">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "Email") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtEmail" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Email") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Logon">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "LogonName") %>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox id="txtLogon" Width="100%" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "LogonName") %>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="PDA">
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "PDA") %>
</ItemTemplate>
<EditItemTemplate>
<asp:CheckBox id="ckPDA" runat="server" Checked ='<%# DataBinder.Eval(Container.DataItem, "PDA") %>'>
</asp:CheckBox>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Training">
<EditItemTemplate>
<asp:DropDownList runat="server" id="dpTraining">
<asp:ListItem Value="0">Init Training</asp:ListItem>
<asp:ListItem Value="1">Retraining</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="Date">
<EditItemTemplate>
<asp:textbox runat="server" id="txtDate"></asp:textbox>
<asp:CompareValidator id="vrDate" runat="server" ErrorMessage="*date*" ControlToValidate="txtDate" Type="Date" Operator="DataTypeCheck"></asp:CompareValidator>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="PID" SortExpression="PID" ReadOnly="True" HeaderText="Physician ID">
<HeaderStyle Font-Names="arial"></HeaderStyle>
</asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Left" ForeColor="#003399" Position="Top" BackColor="#99CCCC" Mode="NumericPages"></PagerStyle>
</asp:datagrid>
yan19454:
I used this line. it have error.
comUpdate.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.p_PDA", System.Data.SqlDbType.Bit, "PDA")).Value = CType(e.Item.FindControl("ckPDA"), CheckBox).Checked
Please please please always post the error encountered, not just the fact that you have an error.
Subscribe to:
Posts (Atom)