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

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

royalcyberAsked:
Who is Participating?
 
Chris MangusDatabase AdministratorCommented:
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.
    Uses:
    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)
As

-- 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)
0
 
slightwv (䄆 Netminder) Commented:
What database?

Also, please define "similar values".

ie./
should "123 Main Street" and "123 Main Ave." match?
0
 
Chris MangusDatabase AdministratorCommented:
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?
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
royalcyberAuthor Commented:
sorry about the confusion

but i was looking for exact data
0
 
royalcyberAuthor Commented:
and it is a Sql Server 2000 database
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
gstiegerCommented:
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
...
?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.