CODEWITHSUNDEEP
sql-serverstored-procedures
Mr Cricket
Mr Cricket

Reputation: 483

How to return bool from stored proc

I'm trying to work out how to write a store procdure which returns a boolean value. I started off writing the following one which returns an int.

USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[ReturnInt]    Script Date: 09/30/2010 09:31:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[ReturnInt]  AS
RETURN 3

I'm unsure however how to write one to return a boolean value.

Can somebody help? Is it a bit value?

Upvotes: 25

Views: 74276

Answers (4)

Arun Solanki
Arun Solanki

Reputation: 184

DECLARE @bitVariable bit     
SELECT @bitVariable = columnName FROM table WHERE id=id

Upvotes: 0

Xin
Xin

Reputation: 36550

You have at least 2 options:

SELECT CONVERT(bit, 1)
SELECT CAST(1 AS bit)

Upvotes: 8

algreat
algreat

Reputation: 9002

Use this:

SELECT CAST(1 AS BIT)

Upvotes: 30

Martin Smith
Martin Smith

Reputation: 453406

You can't. There is no boolean datatype and the procedure return code can only be an int. You can return a bit as an output parameter though.

CREATE PROCEDURE [dbo].[ReturnBit] 
@bit BIT OUTPUT
AS
    BEGIN
    SET @bit = 1
    END

And to call it

DECLARE @B BIT
EXEC [dbo].[ReturnBit] @B OUTPUT
SELECT @B

Upvotes: 35

Related Questions