How to compare Fields of two related tables with input parameters of sp

Question

0.00/5 (No votes)

See more:

I have two related table.
table 1:
----------------
ID int not null,
FirstName char(10) null,
LastName char(10) null
================

table2:
-----------------
pID int not null,
FK_ID int null,
Mail char(10) null,
Phone char(10)null
=================

now in a sp I want compare values of input parameters with values of these tables:

SQL

CREATE PROCEDURE [dbo].[CompareValus]
(@FirstName char(10),
@LastName char(10),
@Mail char(10),
@Phone char(10),
@result bit output
)
as
begin
  DECLARE @ID INT
  DECLARE @pID INT
  select  @ID=ID from table1 where FirstName=@FirsName and LastName=@LastName)
  select @pID=pID from table2 where FK_ID=@ID

--?????????
 if(FirstName=@FirsName and
    LastName=@LastName and
    Mail=@Mail and
    Phone=@Phone ) 
  @result=1
--?????????
end

the problem is I want comparison happen exactly in @ID in table1 and @pID in table2
but I dont know how.

(I'm using sql 2005 so I cant use temp tables)

Posted 17-Feb-15 17:32pm

mit62

Updated 17-Feb-15 17:47pm

v2

Add a Solution

Comments

Rajesh waran 17-Feb-15 23:40pm

What is your expecting output? i think you are in need of Joins. Can you show some table records?

mit62 17-Feb-15 23:46pm

I declare Tables in my question, My expecting output is @result.
if all input parameters be equal with Field of two table then @result=1

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-02-17T18:03:00

If I understood you correctly, you need to know if a record exists in both tables based on the ID and FK_ID fields. If that's true, you can use a simple join query like

SQL

SELECT COUNT(*)
FROM Table1 a 
     INNER JOIN Table2 b ON a.ID = b.FK_ID
WHERE a.FirstName = @FirstName
AND   a.LastName  = @LastName
AND   b.Mail      = @Mail
AND   b.Phone     = @Phone

That should the amount of rows that are matching and zero if none.