Wasn't really familiar with this feature of SQL Server until today.
I have a 4-character field (BidTypeID) in SQL server which actually contains integers (pardon the design issues here people) which i have configured as flaggable enumerations in C#. My stored procedure filtering looks somewhat like this (BidTypeAccess is of type integer):
WHERE (A.BidTypeID & B.BidTypeAccess > 0)
I was revising some of my stored procs this afternoon when I bumped into some of my old SPs with an explicit conversion of the said field to an integer before doing the masking.
WHERE (CAST(A.BidTypeID AS INTEGER) & B.BidTypeAccess > 0)
I have been programming in .NET for two years and the code above was a result of being exposed mainly to a strongly-typed language. Wondering why both codes worked, I posted a query at the public sqlserver newsgroups where I encountered about DataType Precedence and consulted SQL BOL. Basically, SQL server does an implicit conversion of the operands before performing an operation. In this case, the operand of the lower precedence, a CHAR(4), is converted to the data type of the operand with the higher precedence, an INTEGER, which is my desired result.
A responder quoted it being breakable specially for future release and service packs of SQL Server and a feature that one shouldn't really rely upon, STRONGLY AGREE!
FROM BOL:
This is the precedence order for the Microsoft® SQL Server™ 2000 data types:
- sql_variant (highest)
- datetime
- smalldatetime
- float
- real
- decimal
- money
- smallmoney
- bigint
- int
- smallint
- tinyint
- bit
- ntext
- text
- image
- timestamp
- uniqueidentifier
- nvarchar
- nchar
- varchar
- char
- varbinary
- binary (lowest)
Posted
Nov 08 2005, 10:37 PM
by
jokiz