How to write a Sql stored procedure to check if data exists

Posted on 2006-05-17
Medium Priority
Last Modified: 2012-05-05
i need to write a stored procedure to check if the first_name and last_name from the Customer table and Address1, Address 2, city, state and zip from the address table are same. if all the 7 fields have similar values in a record return true, otherwise return false

Please if anyone can help me how to write this SP or have some similar query i can look into. Any help will be greatly appreciated

Question by:royalcyber
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 16701774
What database?

Also, please define "similar values".

should "123 Main Street" and "123 Main Ave." match?
LVL 17

Expert Comment

by:Chris Mangus
ID: 16701780
Please clarify a bit.  First you're saying you want the 7 fields to match.  Then you're saying you want to know if they contain similar data.

Do you want to know if you have similar data or exact data?

Author Comment

ID: 16702269
sorry about the confusion

but i was looking for exact data
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.


Author Comment

ID: 16702275
and it is a Sql Server 2000 database
LVL 17

Accepted Solution

Chris Mangus earned 1500 total points
ID: 16703030
Here is a very rudimentary sample.  I would enhance it by checking for NULL values being input to the SP which are crucial to the success of it's operation.

/* Procedure Name:  myProc
    Procedure Purpose: Returns a flag to the user indicating whether the customer and address exist in the DB.
    Original Author and Date: C.Mangus - 5/17/2006
    Revision History: None

Create Procedure myProc
      @first_name varchar(50),
      @last_name varchar(50),
      @Address1 varchar(100),
      @Address2 varchar(100),
      @city varchar(100),
      @state varchar(50),
      @zip varchar(15)

-- Find out if we have a customer with this name.  If we don't, no need to go further.
If (Select Count(first_name) From Customer Where first_name = @first_name And last_name = @last_name) < 1
      Return (0)

-- If we had the customer name, we'll end up here.  If we don't have this exact address we'll go no further.
If (Select Count(*) From Address Where address1 = @address1 And [address 2] = @address2 And city = @city And state = @state And zip = @zip) < 1
      Return (0)

-- If we had both the customer name and the exact address we'll inform the user with the @myVal return variable
Return (1)
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16705638
Better to use 'IF EXISTS' as this

IF EXISTS (SELECT 1 FROM Customer Where first_name = @first_name And last_name = @last_name )
  RETURN -201

Expert Comment

ID: 16721346
Not sure what the question is.

Do you want to join the tables as in:
if      exists(
      select      1
      from      Customer
      join      Address
      on      Address.CustomerID = Customer.ID
      where      first_name = @first_name
      and      last_name  = @last_name
      and      Address1 = @Address1
      and      Address2 = @Address2
      and      City       = @City
      and      State       = @State
      and      Zip       = @Zip)
return 1 -- true

return 0 -- false

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question