Need help for bit data type

i want to write a generic sql where i want show yes or 开发者_开发百科not if field data type is bit. here i need to check the data type if data type is bit then it should show yes or no based on value 0 or 1.

select stock_code,makeid,modelid,enginesize,automatic,semiautomatic,manual from VehicleInfoForParts

so in my above sql there is bit type fields are automatic,semiautomatic,manual. so here i need to show yes/no but i dont want to hard code anything. so please guide me what would be the best approach for generic sql statement.

can i join my table with system table called information_schema.columns to fetch filed name , value and data type. so result would be like

Column_Name     Value               datatype

------------- ------- --------------

stock_code A112 varchar

automatic 1 bit

semiautomatic 0 bit

manual 1 bit

this type of output can we have just joining my sql with information_schema.columns. if possible then please provide me the right sql which will give me the above sort of output. thanks

please guide. thanks

You could use case for that:

select  case bit_field when 1 then 'yes' else 'no' end as ColumnAlias
...

Create a lookup table for Boolean values.

CREATE TABLE dbo.Boolean
(
    Id bit PRIMARY KEY
    , YesNo varchar(3) UNIQUE
    , TrueFalse varchar(10)
)    
INSERT INTO dbo.Boolean VALUES (0, 'No', 'False')
INSERT INTO dbo.Boolean VALUES (1, 'Yes', 'True')

Then join to the Boolean table for each bit column.

SELECT v.stock_code, v.makeid, v.modelid, v.enginesize
    , a.YesNo automatic, s.YesNo semiautomatic, m.YesNo manual
FROM dbo.VehicleInfoForParts v
LEFT OUTER JOIN dbo.Boolean a ON a.Id = v.automatic
LEFT OUTER JOIN dbo.Boolean s ON s.Id = v.semiautomatic
LEFT OUTER JOIN dbo.Boolean m ON m.Id = v.manual

I recommend doing this in your application, not in the database. When you load your data from SQL Server into, say, an object in your application, handle this in the ToString method of the field you use for your database bit column (assuming you use .NET, if not, use something similar).

You can use the implicit conversion:

select stock_code,makeid,modelid,enginesize,
       CASE WHEN automatic = 1 THEN 'Yes' ELSE 'No' END as automatic,
       CASE WHEN semiautomatic = 1 THEN 'Yes' ELSE 'No' END as semiautomatic,
       CASE WHEN manual = 1 THEN 'Yes' ELSE 'No' END as manual
from VehicleInfoForParts