473,551 Members | 2,797 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

24 New Member
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 = 'ExampleDatabas e')
DROP DATABASE [ExampleDatabase];
GO

CREATE DATABASE [ExampleDatabase];
GO

USE [ExampleDatabase];
GO

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

CREATE TABLE
dbo.ExampleTabl e
(
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(Ph one)
);
GO

SELECT * FROM ExampleTable;
GO

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

SELECT * FROM ExampleTable;
GO

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

SELECT * FROM ExampleTable;
GO

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

SELECT * FROM ExampleTable;
GO


USE [ExampleDatabase];
GO

IF EXISTS(SELECT NAME FROM SYS.PROCEDURES WHERE NAME = 'CheckForeignKe yFax')
DROP PROCEDURE dbo.CheckForeig nKeyFax;
GO

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

RETURN @ResultFax


DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKey Fax @Fax = '11111111'
SELECT ReturnValue=@Re turnValue;
GO


DECLARE @ReturnValue INT
EXEC @ReturnValue = CheckForeignKey Fax @Fax = NULL --RETURNS -1
--EVEN THOUGH Phone has NULL. Please help?
SELECT ReturnValue=@Re turnValue;
GO
Nov 1 '09 #1
4 5589
ck9663
2,878 Recognized Expert Specialist
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
qwedster
24 New Member
Thanks buddy. You hit the nail on its head!
Nov 3 '09 #3
qwedster
24 New Member
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 = CheckForeignKey Fax @Fax = '' -- FAILS
SELECT ReturnValue=@Re turnValue;
GO

Please help.
Nov 3 '09 #4
ck9663
2,878 Recognized Expert Specialist
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

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

Similar topics

2
11848
by: Jonathan | last post by:
I am looking for a simple way to check if a database table exists. I keep getting advice to use "Try.. Catch" and other error handling methods, but I obviously don't want to have to display an error message and stop the process every time someone loads the script after the table is created because that would mean the page could only ever run...
2
17885
by: adam | last post by:
hello What query shoul I send to SQL serwer ( in transact SQL language ) to check does some database exist on serwer ? It similar to problem "does some table exist in database" - resolve to it is query: use db_silnik IF EXISTS (SELECT * FROM prad) PRINT 'table exist'
5
2425
by: Anthony Robinson | last post by:
Consider the following tables: CREATE TABLE "AIMD "."CHANNELSESSION" ( "CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) , "CONNECTSTARTTIME" TIMESTAMP NOT NULL , "CONNECTENDTIME" TIMESTAMP , "ACTIVESESSION" VARCHAR(1), "CHANNELID" DECIMAL(12,0) NOT NULL ) IN...
9
7241
by: Carl Fenley | last post by:
I am successfully adding stored procedures to an Access database. However, I need to be able to check if the stored procedure of the same name already exists. Is there a way to do this other than waiting for the OleDbException caused when adding one that already exists? Here is the code snippet: Private Sub CreateStoredProcedures()
1
2329
by: Phil Mc | last post by:
Trying to call a stored proc but some times don't want to have values inserted in some fields. Hi I am rewriting a VBS script which called a stored proc in a SQL server db. The proc takes a number of values both char and floats. Sometime it is a requirement that the stored proc will not be given a values for some of the floats.
16
5642
by: Brian Tkatch | last post by:
Is there a way to check the order in which SET INTEGRITY needs to be applied? This would be for a script with a dynamic list of TABLEs. B.
0
2379
by: eduardasm | last post by:
Hello, I have a problem with XML schema update for one XML column (problem exists in both SP1 and SP2 for SQL Server 2005). 1. I have a table that looks like this: CREATE TABLE .( NOT NULL DEFAULT (newid()), (500) NOT NULL, NULL,
14
2116
by: Dan | last post by:
Hello, we have an intranet application using Windows Integrated Authentification. When an user starts the application, he gets a form for inputting data. The first time he does that, the application creates in a specific database a table with the name of his account (read with Request.ServerVariables("remote_user") and creates in that table...
2
14645
by: qwedster | last post by:
Folk! How to programattically check if null value exists in database table (using stored procedure)? I know it's possble in the Query Analyzer (see last SQL query batch statements)? But how can I pass null value as parameter to the database stored procedure programattically using C#? Although I can check for empty column (the...
0
7565
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7768
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7847
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6106
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5406
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5130
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3534
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3520
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1097
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.