Create implicit conversion rule for custom data type

Question

I am writing a load of SQL to output .xml files for our customers. They prefer to have "Y" or "N" in BIT columns, rather than 1 or 0. We have a lot of BIT columns all over the place and I need to write CASE WHEN 1 THEN 'Y' ELSE 'N' END more times than I have had hot dinners, to convert the data.

In a sense, that's fine and I should just get on with it and stop moaning, but I couldn't help wondering... If I created a custom datatype from a CHAR(1) base, which only allowed "Y" or "N" values, could I somehow make a rule for implicit conversion so that the machine will do the conversion for me reliably - converting 1 to "Y" and 0 to "N" every time?

I realise that there are other possibilities for achieving the same thing (using a .xsd to convert after the event to have Y/N instead of 1/0 for instance), but I'm interested in adding the custom type and the rule - is it possible and would it be sensible?

Answer 1

There is - AFAIK - no way to get your hands onto these internal processes.

From your question I take, that you are writing an XML file, so I assume, that you want the written letter in your XML files for better human readability? If so, better try to convince your people, that there's nothing wrong with 0 and 1.

But you might save some typing:

 CREATE FUNCTION dbo.WriteBit(@b BIT) RETURNS VARCHAR(1)
 AS BEGIN
    RETURN
    (
    SELECT CASE @b WHEN 1 THEN 'Y'
                   WHEN 0 THEN 'N'
                   ELSE NULL END
    ); 
 END;
 GO

DECLARE @mockup TABLE(SomeInt INT, SomeBit BIT);
INSERT INTO @mockup VALUES
 (0,0)
,(1,1);

SELECT SomeInt,dbo.WriteBit(SomeBit) AS SomeBitChar FROM @mockup;

SELECT SomeInt,dbo.WriteBit(SomeBit) AS SomeBitChar 
FROM @mockup
FOR XML PATH('test'),ROOT('root');
GO

DROP FUNCTION dbo.WriteBit;
GO

The result in XML

<root>
  <test>
    <SomeInt>0</SomeInt>
    <SomeBitChar>N</SomeBitChar>
  </test>
  <test>
    <SomeInt>1</SomeInt>
    <SomeBitChar>Y</SomeBitChar>
  </test>
</root>