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

11,210

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

11,210

Author by

Ian R. O'Brien

I've been developing software for the financial services industry for almost seven years. I work in the investment banking arm of a large multi-national bank. My experience on the sell side includes working on a financial CRM system used by equity research, sales, and trading firms in order to distribute research, discover new sales opportunities, and manage interactions with existing and prospective clients. On the buy side I've worked as a consultant for several different hedge funds in order to develop and implement customized software solutions to automate tasks, improve workflow efficiency, and enforce compliance to the SAS 70 auditing standard. I am experienced in multi-tier architecture development using C#, Java, C++, T-SQL/MySQL, JSP, and HTML, and I have used many other languages and technologies. I received a Bachelor's of Science in Computer Engineering from the Thomas J. Watson School of Engineering and Applied Science from Binghamton University. I am conversationally proficient in Norwegian. I am a Duolingo Global Ambassador for the Norwegian Language, and I study the language at the NYU School of Professional Studies and remotely with a private tutor based in Oslo. I host and maintain the site https://www.brunost.org.

Updated on June 21, 2022

Comments

Ian R. O'Brien about 2 years
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?
Ian R. O'Brien about 11 years

That worked thanks. Could you elaborate on why it worked for a table but not the bit? That might help other people in the future. Thanks again - I will accept your answer when the time limit has elapsed.
João Paladini about 11 years

@Avitus Your Table example is a special kind of UDF(user-defined function) called an "Inline Table-Valued function". They have a different definition format compared to Multi-line Table-Valued functions and to scalar functions like this (which are always multi-line).