By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,410 Members | 1,626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,410 IT Pros & Developers. It's quick & easy.

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

P: 24
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
Nov 1 '09 #1

✓ answered by ck9663

Option 1:

Expand|Select|Wrap|Line Numbers
  1. WHERE isnull(Phone,'NULL') = isnull(@Fax,'NULL')
  2.  
Option 2:

Expand|Select|Wrap|Line Numbers
  1. WHERE (@Fax is NULL and Phone is NULL) or (@Fax = Phone)
  2.  
You might also consider how you treat NULL values entirely.

Good luck!!!

--- CK

Share this Question
Share on Google+
4 Replies


ck9663
Expert 2.5K+
P: 2,878
Option 1:

Expand|Select|Wrap|Line Numbers
  1. WHERE isnull(Phone,'NULL') = isnull(@Fax,'NULL')
  2.  
Option 2:

Expand|Select|Wrap|Line Numbers
  1. WHERE (@Fax is NULL and Phone is NULL) or (@Fax = Phone)
  2.  
You might also consider how you treat NULL values entirely.

Good luck!!!

--- CK
Nov 2 '09 #2

P: 24
Thanks buddy. You hit the nail on its head!
Nov 3 '09 #3

P: 24
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.
Nov 3 '09 #4

ck9663
Expert 2.5K+
P: 2,878
In your code:

Expand|Select|Wrap|Line Numbers
  1. DECLARE @ReturnValue INT
  2. EXEC @ReturnValue = CheckForeignKeyFax @Fax = '' -- FAILS
  3. SELECT ReturnValue=@ReturnValue;
  4. GO
  5.  
@Fax is not NULL, it has a value which is blank/empty.Do you consider NULL, space(1) and '' all the same?

--- CK
Nov 3 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.