CODEWITHSUNDEEP
t-sqluser-defined-functions
Ian R. O'Brien
Ian R. O'Brien

Reputation: 6920

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

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?

Upvotes: 9

Views: 14077

Answers (3)

Kenlly Acosta
Kenlly Acosta

Reputation: 851

Just copy, paste and change as nedded.

ALTER FUNCTION dbo.FUNCTION_NAME (@Param1 char(10))
RETURNS Bit
AS
BEGIN
    Return Case 
        When Exists (Select 1 FROM schema.tableName
                     Where column1 = @Param1)
            Then 1 Else 0
        End;
END
GO

Upvotes: 0

ΩmegaMan
ΩmegaMan

Reputation: 31656

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;

Upvotes: 0

Avitus
Avitus

Reputation: 15958

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

Upvotes: 15

Related Questions