CODEWITHSUNDEEP
stored-proceduresbooleanreturn
user3409439
user3409439

Reputation: 41

I need to return true or false in procedure

I need help with this stored function:

  1. The function should return a Boolean.
  2. If row exists given a couple of parameters than return True or 1 (False or Zero).
  3. Function must accept 2 parameters.

.

CREATE PROCEDURE ( @id varchar(12), @subvarchar(12))

IF EXISTS(SELECT * FROM contacts WHERE id = @id AND subid = @subid )
.....//return true else  ....//return false

I need to return true or false.

Upvotes: 1

Views: 4514

Answers (1)

StuartLC
StuartLC

Reputation: 107277

TL;DR
I would use a function (scalar, or possibly table-valued but this would require a change to a set-based approach)

SqlFiddle of the various options here:

Details
There is no boolean type in SQL Server - the closest is a BIT (0 / 1). Stored procedures can only return an INT, although it is unusual to (ab)use the return value in this way, as it is intended to indicate the success or failure of the procedure.

CREATE PROCEDURE dbo.MyProc( @key varchar(12), @subkey varchar(12)) AS
  RETURN (
    SELECT CASE
      WHEN exists
       (select * from contacts where [key]=@key and [subkey]=@subkey) 
        THEN 1 
        ELSE 0
      END);

More commonly, you would use an OUTPUT parameter:

CREATE PROCEDURE dbo.MyOutputParamProc( @key varchar(12), @subkey varchar(12), @result BIT OUTPUT) AS
  SET @result =
    CASE
      WHEN exists
       (select * from contacts where [key]=@key and [subkey]=@subkey) 
        THEN 1 
        ELSE 0
      END;
GO

Alternatively, you could SELECT a result, which would return a single row, single column.

CREATE PROCEDURE dbo.MyProc( @key varchar(12), @subkey varchar(12)) AS
  SELECT CASE 
      WHEN exists
       (select * from contacts where [key]=@key and [subkey]=@subkey) 
        THEN 'true' 
        ELSE 'false'
      END AS 'Result';

That said, since you aren't changing data, a User Defined Function could be preferable to a PROC in this case, e.g.:

CREATE FUNCTION dbo.MyFunc(@key varchar(12), @subkey varchar(12))
RETURNS BIT
AS BEGIN
  RETURN (SELECT CASE 
      WHEN exists(select * from contacts where [key]=@key and subkey=@subkey) 
        THEN 1 
        ELSE 0
      END);
END;

Called like so:

SELECT dbo.MyFunc('key', 'subkey')

One last point - if you need to retrieve several keys at the same time, and if performance is important, then you might also need to consider approaching this problem in a set-based manner, e.g. by accepting a Table Valued Parameter to your proc / function.

Upvotes: 1

Related Questions