469,267 Members | 952 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,267 developers. It's quick & easy.

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

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

4 5342
ck9663
2,878 Expert 2GB
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
Thanks buddy. You hit the nail on its head!
Nov 3 '09 #3
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
2,878 Expert 2GB
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.

Similar topics

5 posts views Thread by Anthony Robinson | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.