How to check if a value (null/non null) exists in database table using stored pro

[qwedster]

Howdy folks!

I am using stored procedure to see if a value exists in database table and return 0 if exists or else -1, in the following SQL queries.

However how to check if a value (that is null) exists in database table and return 0 if exists or else -1 using stored procedure? Please help.

USE [master]
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'ExampleDatabase')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

IF EXISTS (SELECT NAME FROM SYS.TABLES WHERE NAME = 'ExampleTable')
DROP TABLE dbo.ExampleTable;
GO

CREATE TABLE
dbo.ExampleTable
(
Name NVARCHAR(50) NULL,
Phone NVARCHAR(50) NULL,
Fax NVARCHAR(50) NULL
CONSTRAINT UNIQUE_Phone UNIQUE(Phone),
CONSTRAINT FK_Fax FOREIGN KEY(Fax) REFERENCES ExampleTable(Phone)
);
GO

SELECT * FROM ExampleTable;
GO

INSERT INTO dbo.ExampleTable
(
Name,
Phone,
Fax
)
VALUES
(
'Bill',
'11111111',
'11111111'
);
GO

SELECT * FROM ExampleTable;
GO

INSERT INTO dbo.ExampleTable
(
Name
)
VALUES
(
'Larry'
);
GO

SELECT * FROM ExampleTable;
GO

INSERT INTO dbo.ExampleTable
(
Name,
Phone,
Fax
)
VALUES
(
'Steve',
'77777777',
'11111111'
);
GO

SELECT * FROM ExampleTable;
GO


USE [ExampleDatabase];
GO

IF EXISTS(SELECT NAME FROM SYS.PROCEDURES WHERE NAME = 'CheckForeignKeyFax')
DROP PROCEDURE dbo.CheckForeignKeyFax;
GO

CREATE PROCEDURE dbo.CheckForeignKeyFax
(
@Fax NVARCHAR(50)
)
AS
DECLARE @ResultFax [int]
IF EXISTS
(
SELECT
NULL
FROM
dbo.ExampleTable WITH (UPDLOCK)
WHERE
Phone = @Fax
)
BEGIN
SELECT @ResultFax = 0
END
ELSE
BEGIN
SELECT @ResultFax = -1
END

RETURN @ResultFax


DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKeyFax @Fax = '11111111'
SELECT ReturnValue=@ReturnValue;
GO


DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKeyFax @Fax = NULL --RETURNS -1
--EVEN THOUGH Phone has NULL. Please help?
SELECT ReturnValue=@ReturnValue;
GO

[ck9663]

Option 1:

WHERE isnull(Phone,'NULL') = isnull(@Fax,'NULL')
 

Option 2:

WHERE (@Fax is NULL and Phone is NULL) or (@Fax = Phone)
 

You might also consider how you treat NULL values entirely.

Good luck!!!

--- CK

[qwedster]

Thanks buddy. You hit the nail on its head!

[qwedster]

Hi!

Your answer failed when I called the stored procedure from my C# code as the code actually sends string.Empty or "" as the parameter or when queried using Query Analyzer it'd be like:

DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKeyFax @Fax = '' -- FAILS
SELECT ReturnValue=@ReturnValue;
GO

Please help.

[ck9663]

In your code:

DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKeyFax @Fax = '' -- FAILS
SELECT ReturnValue=@ReturnValue;
GO
 

@Fax is not NULL, it has a value which is blank/empty.Do you consider NULL, space(1) and '' all the same?

--- CK