How to create an SQL function to return a BIT?

Question

I use this script below to create a function, but I get an error message in the message log:

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

Msg 102, level 15, state 31, MyFunction procedure, line 1 Invalid syntax next to "RETURN".

It works when I change this to return a table:

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

therefore, 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 start and end like this:

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