A neat MS SQL treat: convert from count(*) to a bit (or boolean)
I was busy writing a sample application for a retail company where I wanted to express a XACML authorization policy that would state that a user can sell an item if it hasn’t already been sold.
I knew whether the item was sold by simply running a SQL PIP retrieving the count(*) of that item id in the sales contracts. But I wanted to have a boolean attribute in the XACML policy:
A user can sell if the item is not sold (i.e. sold==false). So I went looking around for a means to convert count(*) into a MS SQL bit (the boolean datatype).
And the answer is:
select cast(count(*) as bit) as sold from salecontracts where identifier='9700E2EA-8545-4F26-91A1-1F5E7953E3A4'