How do I create a SQL Function to return a BIT?

Question

I am using this script below to create a function but I get an error in the messages log:

CREATE FUNCTION [dbo].[MyFunction] () RETURNS BIT AS RETURN CAST(1 AS BIT) 

Msg 102, Level 15, State 31, Procedure MyFunction, Line 1 Incorrect syntax near 'RETURN'.

It works when I change this to return a table:

CREATE FUNCTION [dbo].[MyFunction] () RETURNS TABLE AS RETURN (SELECT 1 [1])

so I am not sure what is wrong. Why does this work for a table but not a bit?

Answer 1

Change your syntax to include a begin and end like so:

CREATE FUNCTION [dbo].[MyFunction]()
RETURNS bit 
AS 
begin 
RETURN CAST(1 AS bit) 
end

Answer 2

For a more real life example than just returning a bit value in the accepted answer, here is useful code to check against another table and return a bit

return case when exists (select 1 from Ref.Detail RD
                        where RD.DetailId = @statusDetailId
                        and RD.DetailFlagId = -3)
                        -- -3 is Canceled
            then 1 else 0 end;