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

Posted on 2006-05-17
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 76

    Expert Comment

    by:slightwv (䄆 Netminder)
    What database?

    Also, please define "similar values".

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

    Expert Comment

    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

    sorry about the confusion

    but i was looking for exact data

    Author Comment

    and it is a Sql Server 2000 database
    LVL 17

    Accepted Solution

    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
    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
    LVL 2

    Expert Comment

    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

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduction: I have seen many questions on EE and elsewhere, asking about how to find either gaps in lists of numbers (id field, usually) ranges of values or dates overlapping date ranges combined date ranges I thought it would be a good …
    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now