如何比较sp的输入参数的两个相关表的字段 [英] How to compare Fields of two related tables with input parameters of sp

问题描述

我有两张相关的桌子。

表1：

----------------

ID int not null，

FirstName char（10）null，

LastName char（10）null

====== ==========



table2：

-------------- ---

pID int not null，

FK_ID int null，

邮件char（10）null，

电话char（10）null

=================



现在在我希望将输入参数的值与这些表的值进行比较：

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:

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

问题是我希望比较恰好发生在table1和@中的@ID中表2中的pID

但是我不知道如何。



（我使用sql 2005所以我不能使用临时表）

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)

推荐答案

如果我理解正确，您需要知道两个表中是否存在基于ID和FK_ID字段的记录。如果这是真的，你可以使用简单的连接查询，例如

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

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.

这篇关于如何比较sp的输入参数的两个相关表的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！